Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - OUTPUT - Receiving Output Values from Stored Procedures

By: FYIcenter.com

(Continued from previous topic...)

How To Receive Output Values from Stored Procedures?

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

(Continued on next topic...)

1. What Are Stored Procedures?
2. How To Create a Simple Stored Procedure?
3. How To Execute a Stored Procedure?
4. How To List All Stored Procedures in the Current Database?
5. How To Drop an Existing Stored Procedure?
6. How To Create a Stored Procedure with a Statement Block?
7. How To End a Stored Procedure Properly?
8. How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
9. How To Get the Definition of a Stored Procedure Back?
10. How To Modify an Existing Stored Procedure?
11. How To Create Stored Procedures with Parameters?
12. How To Provide Values to Stored Procedure Parameters?
13. What Are the Advantages of Passing Name-Value Pairs as Parameters?
14. Can You Pass Expressions to Stored Procedure Parameters?
15. How To Provide Default Values to Stored Procedure Parameters?
16. How To Define Output Parameters in Stored Procedures?
17. How To Receive Output Values from Stored Procedures?
18. How To Create a Local Temporary Stored Procedure?
19. Can Another User Execute Your Local Temporary Stored Procedures?