Home > SQL Tips > Using OUTPUT Parameters in Stored Procedures

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. Lets 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

MDSN Links: http://msdn.microsoft.com/en-us/library/aa214332(v=SQL.80).aspx

About these ads
  1. Abi
    May 15, 2012 at 4:43 pm | #1

    Hi Manoj,
    Thanks this is really good. I need some help on stored procedure, like getting a value from a stored procedure and then pass it on as a calling parameter. I am not able to make it working.

  2. Vineet Tomar
    June 14, 2012 at 1:27 am | #2

    Thanx! Good for understanding.

  3. prasanna
    November 10, 2012 at 2:58 pm | #3

    Can u differentiate between out and output parameter in procedures….?

    • November 12, 2012 at 1:00 pm | #4

      There is no difference b/w OUT & OUTPUT parameters in Stored Procedures, they can be used interchangeably.

      For more info check this link in MSDN: http://msdn.microsoft.com/en-us/library/ms187926.aspx, search for “C. Using OUTPUT parameters”.

      They have displayed their usage and are identical, so not to confuse.

  4. zeleke
    June 12, 2013 at 11:31 pm | #5

    The article you are posting is very interesting and understandable.
    i have one question to you
    what do mean by person.contact from the above select statment ? or
    what is your table name?

    • June 12, 2013 at 11:34 pm | #6

      Hi Zeleke, thanks for your comments, and I appreciate you like my posts.

      By person.contact in the SELECT statement I meant that “person” is the schema name and “contact” is the table name. Schema name is usually by default dbo in every database, but you create tables by creating schemas.

      • zeleke
        June 13, 2013 at 12:40 am | #7

        thanks for your fast response

  5. zeleke
    June 13, 2013 at 12:50 am | #8

    i tried many times in my stored procedure (i use sql server 2005 ) it returns null to column values
    the code is the same as the above unless the schema in my case (dbo.mytablename)

    pls help me if u can or any other mechanisim on how to hold outparameter of a stored procedure values in a variable

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 380 other followers

%d bloggers like this: