Stored Procedures: Returning Data
By Bill Graziano on 09 April 2001
This article discusses three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement. Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results. (This article has been updated through SQL Server 2005.)
All the examples in this article use the AdventureWorks database and have been tested through SQL Server 2008. All the client code examples are written using ASP.NET 2.0.
Result Sets
Result sets are what you get when you run a simple SELECT statement inside a stored procedure. Let's suppose you want a stored procedure to return a list of all the people with a given last name. The code for the stored procedure might look like this:
CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
FirstName,
LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
If you just execute this stored procedure in SQL Server Management Studio you get a result set that looks like this:
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ContactID FirstName LastName
----------- ----------------------------------- -----------------------
22 J. Phillip Alexander
23 Michelle Alexander
430 Mary Alexander
. . . { hiding a bunch of rows } . . .
19942 Morgan Alexander
(123 row(s) affected)
1/6 1 2 3 4 5 6 下一页 尾页
