Create a Stored Procedure in SQL SERVER

In this article we are going to see how to create a table and insert the records using the stored procedure and how to out the identity value while inserting the records in table. Also we are going to see how to update the records against the id.

First let we create a Employee table 

/* CREATE A EMPLOYEE TABLE */

CREATE TABLE EMPLOYEE

(

      ID          INT IDENTITY(1,1),

      NAME  VARCHAR(40),

      AGE         INT,

      ADDRES      VARCHAR(150),

      PHONENO VARCHAR(10),

      SALARY      INT

)

Create a stored procedure which will accept three input parameters and one out parameters through which an employee id is out from the stored procedure.

/* CREATE A STORED PROCEDURE TO INSERT THE RECORDS IN THE TABLE*/

CREATE PROCEDURE CREATE_EMP(@NAME VARCHAR(40),@AGE INT,@ADDR VARCHAR(150),@ID INT OUT)

AS

BEGIN

DECLARE @EID TABLE (ID INT)

      BEGIN TRY

           

            INSERT INTO EMPLOYEE

            (

                        NAME,

                        AGE,

                        ADDRES

            )

            OUTPUT INSERTED.ID INTO @EID

            VALUES

            (

                        @NAME,

                        @AGE,

                        @ADDR

            )

      END TRY

      BEGIN CATCH

            SELECT @@ERROR

            SELECT @ID = -1

      END CATCH

SELECT @ID =  ID FROM @EID

END

Exec the Store procedure

/* INSERT THE RECORDS AND GET THE EMPLOYEE UNIQUE ID*/

DECLARE @NAME VARCHAR(40)

DECLARE @AGE INT

DECLARE @ADD VARCHAR(150)

DECLARE @EMPID INT

DECLARE @PHONE INT

SELECT @NAME='HANISH',@AGE = 25, @ADD = 'CHENNAI'

/* STORED PROCEDURE HAVE THREE IN PARAMETER AND ONE OUT PARAMETER i.e IT TAKES THREE PARAMETER AND INSERTS THE RECORDS AND RETURN THE OUTPUT IN OUT DEFINED PARAMETER */

 EXEC CREATE_EMP @NAME,@AGE,@ADD, @EMPID OUT

 SELECT @EMPID AS EMPID

Output:
EMPID
--------
    1



update the records using the ID

/* UPDATE THE PHONE FOR THAT EMPLOYEE IN DATABASE */

 UPDATE EMPLOYEE

 SET  PHONENO     =     '22222222'

 WHERE      ID          =     @EMPID

View the records

 /* SEE THE RECORDS IN EMPLOYEE TABLE */

 SELECT ID,NAME,AGE,ADDRES,PHONENO FROM EMPLOYEE

Output:

I hope from this article you will understand the basic concepts of stored procedures in sql server.