OUTPUT | SQL with Manoj

Using OUTPUT Parameters in Stored Procedures

March 23, 2011 11 comments

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.

Categories: SQL Tips Tags: OUTPUT, OUTPUT Parameter, Stored Procedure

OUTPUT clause and MERGE statement in SQL Server

November 25, 2010 1 comment

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