It’s a fairly common practice to use the OUTPUT clause to return scalar output from a stored procedure. Today’s post is based on a question that I was asked by one of my colleagues related to the usage of OUTPUT clause:
“Can a stored procedure have more than one OUTPUT variables?”
The short answer is Yes! Not only that – it also possible to have non-scalar outputs from stored procedures. But more about that in another post.
Here’s a quick example of a stored procedure having more than one (1) OUTPUT variables. It’s a simple procedure that validates if the input parameters are NULL and subsequently adds two (2) integers. At the end, stored procedure returns 2 variables – the processing state and the result of the addition.
USE [tempdb];
GO
IF OBJECT_ID('dbo.proc_AddNumbersWithMultipleOutputs','P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[proc_AddNumbersWithMultipleOutputs];
END
GO
CREATE PROCEDURE [dbo].[proc_AddNumbersWithMultipleOutputs]
@intA INT,
@intB INT,
@isSuccessfullyProcessed BIT OUTPUT, --Output variable #1
@resultC INT OUTPUT --Output variable #2
AS
BEGIN
SET NOCOUNT ON;
SET @isSuccessfullyProcessed = 0;
SET @resultC = 0;
BEGIN TRY
IF ((@intA IS NULL) OR (@intB IS NULL))
THROW 51000,'Input Parameters cannot be NULL',-1;
SET @resultC = @intA + @intB;
SET @isSuccessfullyProcessed = 1;
END TRY
BEGIN CATCH
SET @isSuccessfullyProcessed = 0;
SELECT ERROR_NUMBER(),
ERROR_MESSAGE(),
ERROR_SEVERITY(),
ERROR_STATE();
END CATCH
END
GO
And here’s the procedure in action. The first query indicates successful execution.
--In a separate window, run the following
DECLARE @inputA INT = 2; --Use NULL to throw exception
DECLARE @inputB INT = 3; --Use NULL to throw exception
DECLARE @outputC INT;
DECLARE @processingState INT;
EXEC [dbo].[proc_AddNumbersWithMultipleOutputs]
@intA = @inputA,
@intB = @inputB,
@isSuccessfullyProcessed = @processingState OUTPUT,
@resultC = @outputC OUTPUT;
SELECT @inputA AS 'Input A',
@inputB AS 'Input B',
@outputC AS 'Result',
@processingState AS 'IsTransactionSuccessfullyProcessed';
GO
The following query with NULL input parameters will result in an exception.
--In a separate window, run the following
DECLARE @inputA INT = 2; --Use NULL to throw exception
DECLARE @inputB INT = NULL;
DECLARE @outputC INT;
DECLARE @processingState INT;
EXEC [dbo].[proc_AddNumbersWithMultipleOutputs]
@intA = @inputA,
@intB = @inputB,
@isSuccessfullyProcessed = @processingState OUTPUT,
@resultC = @outputC OUTPUT;
SELECT @inputA AS 'Input A',
@inputB AS 'Input B',
@outputC AS 'Result',
@processingState AS 'IsTransactionSuccessfullyProcessed';
GO
I trust this simple demonstration will help you to write more effective T-SQL code.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
Pingback: #0432 – SQL Server – Tips – Returning parameters from stored procedures (Part 1 of 2) | SQLTwins by Nakul Vachhrajani