Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL Server (2008): Passing and Capturing Stored Procedure Result

ms sql server stored procedure t-sql sql server 2008 r2 capture output

  • Please log in to reply
No replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 28 April 2013 - 12:43 AM

Overview

Previously we have learned how to create and execute an MS SQL Server stored procedure. The examples are trivial. Now it's time to be more serious on our journey to tame stored procedure. We knew how to pass input data. But like routines in other programming languages, there are many occasions where we need to know the result of the execution.

MS SQL Server stored procedures could return information in four ways. Let me quote this msdn page of those ways.
 

Microsoft® SQL Server™ 2000 stored procedures return data in four ways:

  • Output parameters, 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).
  • Return codes, which are always an integer value.
  • A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
  • A global cursor that can be referenced outside the stored procedure.

 

 
 
Output Parameters

This option is very similar with the input parameters. The only difference is that you need to add keyword OUTPUT at the end of the parameter declaration both when defining the stored procedure and when executing. However when executing you could omit this OUTPUT keyword. However by doing so you will make the parameter into INPUT parameter, and you can not capture whatever value the stored procedure passed through that parameter.

Let's meet our first example of output parameter(s).

CREATE PROCEDURE [dbo].[UseOutputParams]
	@Input char(10)=NULL
	, @Out1 int OUTPUT
	, @Out2 char(10)='0000000000' OUTPUT
AS
BEGIN
	IF @Input IS NOT NULL
	BEGIN
		-- copy @Input to @Out2
		SET @Out2=@Input
		-- Place the length of @Input into @Out1
		SET @Out1=LEN(@Input)
	END
	ELSE
		-- When @Input was not specified, simply set @Out1 to zero
		SET @Out1=0
END

In the above example, we have a stored procedure that has one input parameter with default value of NULL, and two output parameters. Note the OUTPUT keywords after @Out1 and @Out2 parameters? Like input parameter, output parameter can also has default value. However this default value would only be useful in internal calculation (when the parameter omitted upon execution).

Let's execute the stored procedure. Copy paste the following T-SQL commands into your SQL Server client software.

DECLARE @Input1 char(10);
DECLARE @Output1 int;
DECLARE @Output2 char(10);

SET @Input1='12345';
SET @Output1=NULL;
SET @Output2=NULL;

EXEC dbo.UseOutputParams 
		@Input=@Input1
		, @Out1=@Output1 OUTPUT
		, @Out2=@Output2 OUTPUT;

SELECT @Output1 AS Out01, @Output2 as Out02

Upon execution you would get something like below.
001StoreProc_OutputParameterExec01.png


Return Codes

Any stored procedure could return a single integer value upon its successful execution. As the stored procedure writer, you could use this return code to indicate a little bit detail of the result of the execution. For example, you might want to use negative integer values to indicate failures, with different value specifies more specific failure cause.

By default this return code will be 0. You need to customize the return code inside your stored procedure. Do this by specifying the return code when calling RETURN in your stored procedure.
 
Let's modify the previous stored procedure to incorporate custom return codes.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UseOutputParams]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UseOutputParams]
GO

CREATE PROCEDURE [dbo].[UseOutputParams]
	@Input char(10)=NULL
	, @Out1 int OUTPUT
	, @Out2 char(10)='0000000000' OUTPUT
AS
BEGIN
	IF @Input IS NOT NULL
	BEGIN
		-- copy @Input to @Out2
		SET @Out2=@Input
		-- Place the length of @Input into @Out1
		SET @Out1=LEN(@Input)
		RETURN 1
	END
	ELSE BEGIN
		-- When @Input was not specified, simply set @Out1 to zero
		SET @Out1=0
		RETURN -1
	END
END

Note the "RETURN 1" and "RETURN -1"? Yes, we will return 1 when we passed any value other than NULL for @Input parameter. When we passed NULL for @Input, we will get -1 as return code.
 
Time to see it in action. First we will pass non-null value for @Input parameter. Like below.

DECLARE @Input1 char(10);
DECLARE @Output1 int;
DECLARE @Output2 char(10);
DECLARE @Result int;

SET @Input1='12345';
SET @Output1=NULL;
SET @Output2=NULL;
SET @Result=NULL;

EXEC @Result=dbo.UseOutputParams
		@Input=@Input1
		, @Out1=@Output1 OUTPUT
		, @Out2=@Output2 OUTPUT;

SELECT @Result AS Result, @Output1 AS Out01, @Output2 as Out02;

And you would get something like shown below. Note that I need local variable @Result of integer type to store the return code. Also note how to execute the stored procedure and capture it's return code (green circled)? It's like handling function.

011StoreProc_ReturnCodeExec01.png


Now let's try passing NULL for @Input, and you would get something like below.
012StoreProc_ReturnCodeExec02.png


Result Sets

Inside a stored procedure you can also do one or more SELECT operation(s). The resultset of the SELECT-s operation(s) will be returned upon the return of the stored procedure. In case it's not obvious enough, you can have several result sets from a stored procedure.

Let's try returning several resultset from our stored procedure. Execute the following script in your SQL Server client software to create the ReturnMultiResultsets example.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReturnMultiResultsets]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReturnMultiResultsets]
GO

CREATE PROCEDURE [dbo].[ReturnMultiResultsets]
AS
BEGIN
	SELECT 1 AS [ID], 'AAA' AS [code], '2000-01-01' AS [Date]
	UNION 
	SELECT 2, 'BBB', '2003-02-02'
	UNION 
	SELECT 3, 'CCC', '2003-03-03'
	
	SELECT * FROM Members
	SELECT * FROM EmailAddresses
	SELECT * FROM dbo.vMemberEmailAddresses
END

GO

As you would expect, the execution of this stored procedure will return 4 resultsets. Like shown below.
021StoreProc_ReturnResultset01.png


Global Cursor

This approach actually similar with returning result set above. The difference is that the records will not be returned "immediately" but through another table or table-like entity. I will save the details of this approach for later, since I would like to address cursor and its manipulation first, in future article.

 

 

Sample Database

Here is complete DDL of items in the sample database used in this article, including some sample data. Feel free to use it to setup the sample database in your environment.

Attached File  DDL+SampleData.txt   7.9KB   327 downloads


Edited by LuthfiHakim, 29 April 2013 - 03:14 AM.

  • 0





Also tagged with one or more of these keywords: ms sql server, stored procedure, t-sql, sql server 2008 r2, capture output