Returning Data from Stored Procedures

该文章来自:http://www.cleardata.biz/articles/storedproc.aspx

 

By Bill Graziano (3/20/2002)

This article discusses three common ways to return data from stored procedures:  OUTPUTing variables, temp tables and the RETURN statement.  This article was originally published on SQLTeam.com.  All the examples in this article use the pubs database and MS SQL Server 7. Everything in this article also works in SQL Server 2000. SQL Server does give you some additional options which I'll mention in the article.

Using OUTPUT variables

The first approach uses OUTPUT variables. This will allow us to return values (but not record sets in SQL7) back to the calling procedure. First I'll need a stored procedure:

CREATE PROCEDURE GetStateCount ( @State char(2), @StateCount int OUTPUT )
AS
SELECT @StateCount = Count(*)
FROM authors
WHERE State = @State
go

The OUTPUT statement indicates that @StateCount can be used to return a value back to the calling procedure. Whatever value we put into @StateCount will be passed back to the calling program. All this stored procedure does is count the number of authors from a given state.

The SQL to call this looks like this:

DECLARE @TheCount int
EXEC GetStateCount 
  @State = 'UT', 
  @StateCount = @TheCount OUTPUT
Select TheCount = @TheCount

We use the EXEC command to call a stored procedure. I always specify the name of the arguements as I pass them. Notice that I also had to include the OUTPUT clause in the EXEC statement. After the EXEC statement runs, @TheCount should contain the value from @StateCount inside the stored procedure. The output should show that there are two authors from Utah and it looks like this:

TheCount    
----------- 
2

(1 row(s) affected)

Record Sets

Let's suppose you wanted the stored procedure to return a list of all the authors in a given state. In SQL Server 2000 you can experiment with the TABLE datatype. In SQL Server 7 we'll stick with temp tables. The basic approach is to build a temp table, call a stored procedure to populate it, and then do the processing. My script on the outside looks like this:

CREATE TABLE #Authors (au_id char(11))

INSERT #Authors
EXEC GetStateAuthors @State='UT'

SELECT *
FROM #Authors

DROP TABLE #Authors

This is a variation on using SELECT to INSERT records into a table. The GetStateAuthors procedure looks like this:

CREATE PROCEDURE GetStateAuthors ( @State char(2) )
AS
SELECT au_id
FROM authors
WHERE State = @State
go

Any procedure that returns a record set can be handled like this. I use this quite a bit when dealing with packaged applications. In many cases they use stored procedures to return record sets to screens. I call their procedures, capture the output in my scripts and handle it there. Just make sure your temporary table and the SELECT statement match.

Using Return

The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. Consider this procedure:

CREATE PROC TestReturn (@InValue int)
AS
Return @Invalue
go

All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure:

Declare @ReturnValue int
EXEC @ReturnValue = TestReturn 3
Select ReturnValue=@ReturnValue

and the output looks like this:

ReturnValue 
----------- 
3

(1 row(s) affected)

Whatever number is returned using the RETURN statement is put into the variable @ReturnValue.

That's the three best way I know of to get data from a stored procedure back to a calling stored procedure. Enjoy!