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.

Let’s check this with a simple example by using AdventureWorks database:

USE [AdventureWorks]

--// Create Stored Prcedure with OUTPUT parameter
	@ContactID INT,
	@FirstName VARCHAR(50) OUTPUT,
	@LastName  VARCHAR(50) OUTPUT
	SELECT @FirstName = FirstName, @LastName = LastName
	FROM Person.Contact
	WHERE ContactID = @ContactID

--// Test the Procedure

--/ 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

--// Final Cleanup


>> Check & Subscribe my [YouTube videos] on SQL Server.

  1. Abi
    May 15, 2012 at 4:43 pm

    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

    Thanx! Good for understanding.

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

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

    • November 12, 2012 at 1:00 pm

      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

    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

      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

        thanks for your fast response

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

    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

  6. taj
    September 24, 2014 at 3:18 pm

    i facing problem… i have oracle procedure having one out parameter with datatype sys_refcursor… i have to convert this script into sql server 2008 … then please tell me how to convert this without using any tool just by manually ………

    • September 24, 2014 at 3:23 pm

      Hi Taj,
      If you can post the code snippet of the Oracle SP then I can try to convert, otherwise I don’t have experience in Oracle.


  7. rufeenajack
    November 8, 2018 at 2:27 pm

    hello everyone….can anyone send a code for this

    9) create a stored procedure for the employee table with the following parameters

    empid input parameter,
    first_name output parameter,

    last_name   output parameter,
    salary      output parameter,
    city        output parameter  

    The procedure should accept id as input and returns corresponding employee details
    as output

  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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: