OUTPUT Clause in SQL Server


Output clause was introduced in SQL Server 2005 , it returns value of each row affected into the SQL Statement. OUTPUT Clause can be used  with
             
       1)     Stored Procedure to store output value,
       2)     INSERT UPDATE, DELETE and MERGE Statements.

Here I am writing the ways of OUTPUT clause we may use.


OUTPUT Clause in Stored Procedure


--Create Procedure with OUTPUT Parameter
Create Procedure NextNum @Num1 int, @Num2 int OUTPUT
AS
BEGIN
 SET @Num2 = @Num1 +1
END

--Store calculated value of Procedure using OUTPUT Clause

DECLARE @MyNum1 int, @MyNum2 int
SET @MyNum1 = 1
EXEC NextNum @MyNum1, @MyNum2 OUTPUT
PRINT @MyNum2


In this example i try to demonstrate the OUTPUT clause with procedure. when i declare second parameter of Procedure  with OUTPUT it can return value to variable.


OUTPUT Clause with Magic Tables

OUTPUT uses two magic  tables also known as internal tables INSERTED and DELETED for capturing values of the effected rows based on the action(INSERT, UPDATE, DELETE) taken in that SQL statement. In case of INSERT and UDATE command Magic table INSERTED get filled and when DELETE command fires DELETED Magic table is filled.

/*==========================================
Use INSERTED Magic Tables with OUTPUT Clause to Insert
values in Temp Table or Table Variable
==========================================*/

create table NewEMP(Name varchar(12),Dept varchar(12))
create table #CopyEMP(CName varchar(12),CDept varchar(12))

--Insert values into NewEmp table as well as in Temp Table
Insert into NewEMP(Name,Dept)
 OUTPUT InsertED.Name, InsertED.Dept into #CopyEMP
 values('Ram','IT')


Check values in table NewEMP and Temp Table #CopyEMP. You will find same entry in both the Tables

select * from NewEMP

Name         Dept
------------ ------------
Ram          IT

select * from CopyEMP

CName         CDept
------------ ------------
Ram          IT

/*==========================================
In the same way we can use DELETED magic table
With OUTPUT Clause in DELETE command
==========================================*/

--Create a backup table
create table #XEMP(Name varchar(12),Dept varchar(12))

--in below statement DELETE command will delete
--Records from main table and OUTPUT clause will
--insert that deleted record into XEMP Table

Delete from NewEMP
OUTPUT DELETED.Name, DELETED.Dept INTO #XEMP


Check records in both the table, Now records should deleted  from NewEMP and that deleted records must be available in #XEMP Temp Table


select * from NewEMP
name         dept
------------ ------------

select * from #XEMP
name         dept
------------ ------------
Ram          IT

I hope this is described in easy manner, please let me know if you have any query regarding this topic

1 comment:

  1. How about the following:

    select top 0 * into #XEMP from NewEmp
    go
    Delete from NewEMP
    OUTPUT DELETED.Name, DELETED.Dept INTO #XEMP

    The select top 0 ... creates a blank table with all the fields in NewEmp

    ReplyDelete