OUTPUT - Receiving Output Values from Stored Procedures in SQL Server

Q

How To Receive Output Values from Stored Procedures in SQL Server Transact-SQL?

✍: FYIcenter.com

A

If an output parameter is defined in a stored procedure, the execution statement must provide a variable to receive the output value in the format: "@variable_name OUTPUT" or "@parameter_name = @variable_name OUTPUT". The following tutorial exercise gives you a good example:

-- Using @variable format
DECLARE @message VARCHAR(40);
EXECUTE diff_in_days
   '01-Jan-2007',
   '11-May-2007',
   @message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01, 2007 =        130

-- Using @parameter = @variable format
DECLARE @message VARCHAR(40);
EXEC diff_in_days
   @start_date='01-Jan-2007',
   @end_date='11-May-2007',
   @days = @message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01, 2007 =        130

-- Mixed formats are not allowed
DECLARE @message VARCHAR(40);
EXEC diff_in_days
   @start_date='01-Jan-2007',
   @end_date='11-May-2007',
   @message OUTPUT;
PRINT @message;
GO
Msg 119, Level 15, State 1, Line 2
Must pass parameter number 2 and subsequent parameters 
as '@name = value'. After the form '@name = value' has 
been used, all subsequent parameters must be passed in 
the form '@name = value'.

 

Using Stored Procedures in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2016-12-28, 194👍, 0💬