Tuesday, 5 November 2013

STORED PROCEDURE - 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.

No comments:

Post a Comment