1

Using OUTPUT Parameters in Stored Procedures | SQL with Manoj

 2 years ago
source link: https://sqlwithmanoj.com/2011/03/23/using-output-parameters-in-stored-procedures/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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.

Related


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK