OUTPUT PARAMETER IN SQL SERVER

• OUTPUT PARAMETERS ARE NORMAL STORED PROCEDURE PARAMETERS THAT ARE MARKED WITH THE KEYWORD OUTPUT(OUT for short)

• BESIDES USING INPUT PARAMETERS, STORED PROCEDURES CAN ALSO RETURN OUTPUT PARAMETERS AND RETURN VALUES. OUTPUT PARAMETERS BEHAVE SIMILARLY TO INPUT PARAMETERS, BUT HAVE TO BE DECLARED WITH THE OUTPUT KEYWORD.
•   
•   OUTPUT PARAMETERS ALLOWS YOU TO CAPTURE OUTPUT OF THE STORED PROCEDURE  IN  LOCAL VARIABLE

•  OUTPUT CLAUSE RETURNS THE INFORMATION OF ALLTHE ROWS AFFECTED BY THE INSERT, DELETE,UPDATE& MERGE STATEMENTS 

EXAMPLES:

CREATE SAMPLE TABLE:

CREATE  TABLE TBL_OUTPUT_CLAUSE(ID INT IDENTITY,NAME VARCHAR(30),GENDER VARCHAR(30),EMAIL VARCHAR(50))

DATA INSER INTO TABLE:

INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BABJI','MALE','BABJI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BHAVANI','FEMALE','BHAVANI.MM@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('MANJU','MALE','MANJU.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('BHARATH','MALE','BHARATHREDDY407@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('LAKSHMI','FEMALE','LAKSHMI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('SWATHI','FEMALE','SWATHI.REDDY1@GMAIL.COM')
INSERT INTO TBL_OUTPUT_CLAUSE VALUES('JAIPAL','MALE','JAIPAL.REDDY1@GMAIL.COM')


             1. BY USING OUTPUT CLAUSE TO INSERT IDENTITY VALUE

CREATE PROC USP_OUTPUT_CLAUSE(@ID INT OUT,@NAME VARCHAR(30),@GENDER VARCHAR(30),@EMAIL VARCHAR(50))
AS
BEGIN
      INSERT INTO TBL_OUTPUT_CLAUSE (NAME,GENDER,EMAIL)                                                 VALUES(@NAME,@GENDER,@EMAIL)

      SET @ID=@@IDENTITY

     SELECT * FROM TBL_OUTPUT_CLAUSE


END

  DECLARE LOCAL PARAMETER  WITH SAME DATATYPE 
               OF OUTPUT PARAMETER DATATYPE


DECLARE @OUT_ID INT

EXEC USP_OUTPUT_CLAUSE @ID=@OUT_ID OUT ,@NAME='NAVEEN',@GENDER='MALE',@EMAIL='NAVEEN.KUMAR@GMAIL.COM'

PRINT @OUT_ID



----OUTPUT: 8


2.COUNT  MALES AND FEMALE IN A TABLE BY USING OUTPUT PARAMETER


CREATE PROC USP_OUTPUT_CLAUSE1(@GENDER VARCHAR(30),@EMP_COUNT INT OUT)
AS
BEGIN
            SELECT @EMP_COUNT=COUNT(ID)  FROM  TBL_OUTPUT_CLAUSE  WHERE                         GENDER=@GENDER


END

• FIRST INITIALISE A VARIABLE OF SAME DATATYPE AS THAT OF THE OUTPUT PARAMETER. HERE WE HAVE DECLARED @OUT_COUNT INTEGER VARIABLE.
• THEN PASS THE @OUT_COUNT VARIABLE TO THE STORED PROCEDURE, YOU HAVE TO SPECIFY THE OUTPUT KEYWORD. IF YOU DON’T SPECIFY THE OUTPUT KEYWORD, THE VARIABLE WILL BE NULL.

DECLARE @OUT_COUNT  INT

EXEC USP_OUTPUT_CLAUSE @GENDER='MALE',@EMP_COUNT=@OUT_COUNT OUT

PRINT @OUT_COUNT

OUTPUT: 4

• IF YOU DONT SPECIFY THE OUTPUT KEYWORD, WHILE EXECUTING THE STORED PROCEDURE, THE @OUT_COUNT VARIABLE WILL BE NULL. IN THE EXAMPLE BELOW, WE HAVE NOT SPECIFIED OUTPUT KEYWORD. SO WHILE EXECUTING THE STORED PROCEDURE, A MESSAGE OF @OUT_COUNT IS NULL IS PRINTED.

DECLARE @OUT_COUNT  INT

EXEC USP_OUTPUT_CLAUSE @GENDER='MALE',@EMP_COUNT=@OUT_COUNT OUT

IF (@OUT_COUNT IS NULL)

   PRINT '@OUT_COUNT IS NULL'

ELSE

   PRINT '@OUT_COUNT IS NOT NULL'

OUTPUT: @OUT_COUNT IS NOT NULL

             3. CAPTURE INSERT,DELETE ,UPDATE BY USING OUTPUT PARAMETER

EX:

CREATE TABLE QQ(ID INT,NAME VARCHAR(50),SAL MONEY)

INSERT INTO QQ VALUES(1,'BABJI',10000)
INSERT INTO QQ VALUES(2,'MANJU',5400)
INSERT INTO QQ VALUES(3,'KAMAL',2000)

SELECT * FROM QQ


CREATE TABLE LOG_QQ_INSERT (ID INT,NAME VARCHAR(50),SAL MONEY)
CREATE TABLE LOG_QQ_DELETE (ID INT,NAME VARCHAR(50),SAL MONEY)


SELECT * FROM LOG_QQ_DELETE
SELECT * FROM LOG_QQ_INSERT

CAPTURE INSERT LOG BY USING OUTPUT CLAUSE:
     

INSERT INTO QQ (ID,NAME,SAL)
OUTPUT INSERTED.* INTO LOG_QQ_INSERT VALUES(4,'NAVEEN',3000)

                                        [OR]


 INSERT INTO QQ(ID,NAME,SAL)

OUTPUT INSERTED.ID,INSERTED.NAME,INSERTED.SAL INTO LOG_QQ_INSERT  VALUES(5,'NAVEEN',3000)

-BY USING TABLE VARIBLE WITH OUTPUT PARAMETER

--------DECLARE @QQ_INSERTED TABLE VARIBLE

DECLARE  @QQ_INSERT TABLE(ID INT,NAME VARCHAR(50),SAL MONEY)

--------INSERT NEW ROW INT QQ TABLE

INSERT INTO QQ
OUTPUT INSERTED.* INTO @QQ_INSERT VALUES(6,'GGG',2999)

-------VIEW INSERTED ROW INTO QQ TABLE

SELECT * FROM QQ

-------VIEW THE OUTPUT ROW IN @QQ_INSERTED

SELECT * FROM @QQ_INSERTED


NOTE: WE RUN ABOVE 4 QUERIES AT A TIME

LINK:
https://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/



CAPTURE DELETE RECORDS BY USING OUT PARAMETER:


DELETE FROM QQ 

OUTPUT DELETED.* INTO LOG_QQQ_DELETE WHERE ID=2
                     
                                  [OR]

DELETE FROM QQ 

OUTPUT DELETED.ID,DELETED.NAME,DELETED.SAL INTO LOG_QQQ_DELETE WHERE ID=3


-BY USING TABLE VARIBLE WITH OUTPUT PARAMETER

--------DECLARE @QQ_DELETED TABLE VARIBLE

DECLARE  @QQ_DELETED TABLE(ID INT,NAME VARCHAR(50),SAL MONEY)

--------INSERT NEW ROW INT QQ TABLE

DELETE FROM QQ 

OUTPUT DELETED.* INTO  @QQ_DELETED WHERE ID=2

-------VIEW DELETED ROW INTO QQ TABLE

SELECT * FROM QQ

-------VIEW THE OUTPUT ROW IN @QQ_DELETED

SELECT * FROM @QQ_DELETED


EX:
CREATE TABLE DBO.BOOKS
(
  BOOKID INT NOT NULL PRIMARY KEY,
  BOOKTITLE  NVARCHAR(50) NOT NULL,
  MODIFIEDDATE DATETIME NOT NULL
);
INSERT INTO BOOKS VALUES(1,'GVGHVS','07-17-1990')
INSERT INTO BOOKS VALUES(2,'GDGAS','07-07-1990')
INSERT INTO BOOKS VALUES(3,'BHVH','08-17-1990')


SELECT * FROM BOOKS

DECLARE @DeleteOutput3 table
(
  BookID int,
  BookTitle nvarchar(50),
  ModifiedDate datetime
);

-- delete row in Books table
DELETE Books
OUTPUT
    DELETED.BookID,
    DELETED.BookTitle,
    DELETED.ModifiedDate
  INTO @DeleteOutput3
OUTPUT
    DELETED.BookID,
    DELETED.BookTitle
WHERE BookID = 1;

-- view updated row in Books table
SELECT * FROM Books;

-- view output row in @DeleteOutput3 variable

SELECT * FROM @DeleteOutput3;

CHECK OUTPUT:


CAPTURE UPDATE RECORDS BY USING OUTPUT PARAMETER:


---CAPTURE ONLY DELETE DATA

UPDATE QQ SET NAME='JAIPAL',SAL=6777
OUTPUT DELETED.* INTO LOG_QQ_DELETE WHERE ID=1

----CAPTURE ONLY INSERT DATA

UPDATE QQ SET NAME='JAIPAL',SAL=6777
OUTPUT INSERTED.* INTO LOG_QQ_DELETE WHERE ID=1

---CAPTURE DELETE AND INSERT DATA 

CREATE TABLE QQ_DELETE_INSERT(ID INT,OLD_NAME  VARCHAR(50), NEW_NAME                                                                  VARCHAR(50),OLD_SAL MONEY,NEW_SAL MONEY)

UPDATE QQ SET NAME='JAIPAL',SAL=5000
OUTPUT  INSERTED.ID,
        DELETED.NAME,
         INSERTED.NAME,
         DELETED.SAL,
         INSERTED.SAL   INTO QQ_DELETE_INSERT  WHERE ID=1

CHECK:   SELECT * FROM QQ
                   SELECT * FROM QQ_DELETE_INSERT


CREATE TABLE DBO.BOOKSS
(
  BOOKID INT NOT NULL PRIMARY KEY,
  BOOKTITLE NVARCHAR(50) NOT NULL,
  MODIFIEDDATE DATETIME NOT NULL
);
INSERT INTO BOOKSS VALUES(1,'BABJI','07-17-1990')
INSERT INTO BOOKSS VALUES(2,'MANJU','07-07-1990')
INSERT INTO BOOKSS VALUES(3,'KAMAL','08-17-1990')

SELECT * FROM BOOKSS


-- DECLARE @UPDATEOUTPUT1 TABLE VARIABLE

DECLARE @UPDATEOUTPUT1 TABLE
(
  OLDBOOKID INT,
  NEWBOOKID INT,
  BOOKTITLE NVARCHAR(50),
  OLDMODIFIEDDATE DATETIME,
  NEWMODIFIEDDATE DATETIME
);

-- UPDATE ROW IN BOOKS TABLE

UPDATE BOOKSS
SET
  BOOKID = 105,
  MODIFIEDDATE = GETDATE()
OUTPUT
    DELETED.BOOKID,
    INSERTED.BOOKID,
    INSERTED.BOOKTITLE,
    DELETED.MODIFIEDDATE,
    INSERTED.MODIFIEDDATE
  INTO @UPDATEOUTPUT1
WHERE BOOKTITLE = 'BABJI';

-- VIEW UPDATED ROW IN BOOKS TABLE

SELECT * FROM BOOKSS;

-- VIEW OUTPUT ROW IN @UPDATEOUTPUT1 VARIABLE

SELECT * FROM @UPDATEOUTPUT1;



example:

-- DECLARE @UPDATEOUTPUT1 TABLE VARIABLE

DECLARE @UPDATEOUTPUT2 TABLE
(
  OLDBOOKID INT,
  NEWBOOKID INT,
  BOOKTITLE NVARCHAR(50),
  OLDMODIFIEDDATE DATETIME,
  NEWMODIFIEDDATE DATETIME,
  DIFFINSECONDS INT
);

-- UPDATE ROW IN BOOKS TABLE

UPDATE BOOKSS
SET
  BOOKID = BOOKID + 1,
  MODIFIEDDATE = GETDATE()
OUTPUT
    DELETED.BOOKID,
    INSERTED.BOOKID,
    INSERTED.BOOKTITLE,
    DELETED.MODIFIEDDATE,
    INSERTED.MODIFIEDDATE,
    DATEDIFF(SS, DELETED.MODIFIEDDATE, INSERTED.MODIFIEDDATE)
  INTO @UPDATEOUTPUT2
WHERE BOOKTITLE = 'KAMAL';

-- VIEW UPDATED ROW IN BOOKS TABLE

SELECT * FROM BOOKSS;

-- VIEW OUTPUT ROW IN @UPDATEOUTPUT2 VARIABLE


SELECT * FROM @UPDATEOUTPUT2;