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.
 

---