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.
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.
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.
Now let's try passing NULL for @Input, and you would get something like below.
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.
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.
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.
DDL+SampleData.txt 7.9KB 436 downloads
Edited by LuthfiHakim, 29 April 2013 - 03:14 AM.