Archive
Using OUTPUT Parameters in Stored Procedures
According to MS-BOL, SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.
Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program.
Let’s check this with a simple example by using AdventureWorks database:
USE [AdventureWorks] GO --// Create Stored Prcedure with OUTPUT parameter CREATE PROCEDURE getContactName @ContactID INT, @FirstName VARCHAR(50) OUTPUT, @LastName VARCHAR(50) OUTPUT AS BEGIN SELECT @FirstName = FirstName, @LastName = LastName FROM Person.Contact WHERE ContactID = @ContactID end GO --// Test the Procedure DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50) --/ Test# 1 SET @CID = 100 EXEC getContactName @ContactID=@CID, @FirstName=@FName OUTPUT, @LastName=@LName OUTPUT SELECT @FName as 'First Name', @LName as 'Last Name' --/ Output -- ContactID First Name Last Name -- 100 Jackie Blackwell --/ Test# 2 SET @CID = 200 EXEC getContactName @ContactID=@CID, @FirstName=@FName OUTPUT, @LastName=@LName OUTPUT SELECT @FName as 'First Name', @LName as 'Last Name' --/ Output -- ContactID First Name Last Name -- 200 Martin Chisholm GO --// Final Cleanup DROP PROCEDURE getContactName GO
>> Check & Subscribe my [YouTube videos] on SQL Server.
OUTPUT clause and MERGE statement in SQL Server
Just responded to a post in MSDN forum, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8
Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Lets go with a self descriptive example:
–> OUTPUT with INSERT
create table manoj (sn int, ename varchar(50)) insert into manoj OUTPUT INSERTED.* values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
select * from manoj
This gives me the same output as above:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
–> OUTPUT with DELETE
delete from manoj OUTPUT DELETED.* where sn = 4
This gives me following output:
sn ename
4 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
–> OUTPUT with UPDATE
update manoj set ename = 'pankaj' OUTPUT DELETED.*, INSERTED.* from manoj where sn = 2
This gives me following output:
sn ename sn ename
2 hema 2 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 pankaj
3 kanchan
–> OUTPUT with MERGE
According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
create table manoj2 (sn int, ename varchar(50)) insert into manoj2 values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh') select * from manoj2
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
MERGE manoj AS TARGET USING (SELECT sn, ename FROM manoj2) AS SOURCE ON (TARGET.sn = SOURCE.sn) WHEN MATCHED THEN UPDATE SET TARGET.ename = SOURCE.ename WHEN NOT MATCHED BY TARGET THEN INSERT (sn, ename) VALUES (sn, ename) OUTPUT $action, DELETED.*, INSERTED.*;
This gives me following output:
$action sn ename sn ename
INSERT NULL NULL 4 pankaj
INSERT NULL NULL 5 saurabh
UPDATE 1 manoj 1 manoj
UPDATE 2 pankaj 2 hema
UPDATE 3 kanchan 3 kanchan
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
5 saurabh
–> Final cleanup
drop table manoj drop table manoj2
Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.
MS BOL:-
– On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx
– On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx