T-SQL : Return Statement

Stored Procedures: Returning Data

---

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)

If you want to capture this result set using T-SQL you'll need a place to store it.  Temporary Tables work well for that.  That code might look something like this:

DECLARE @People TABLE (
ContactID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

(123 row(s) affected)

-----------
123

(1 row(s) affected)

This code let's us capture the result set from the stored procedure into a table variable or temporary table.  I use code like this when I work with packaged applications.  I call their stored procedures to get the results I want and then manipulate it as needed.

Using OUTPUT variables

If you just want to return a single value (or a couple of values) you can use output variables.  The stored procedure looks like this:

CREATE PROCEDURE dbo.GetCountByLastName (
@LastName NVARCHAR(50),
@LastNameCount INT OUTPUT )
AS
SELECT @LastNameCount = COUNT(*)
FROM Person.Contact
WHERE LastName = @LastName

If we want to return the value using T-SQL we'd use code like this:

DECLARE @TheCount INT

EXEC dbo.GetCountByLastName
@LastName = 'Alexander',
@LastNameCount = @TheCount OUTPUT

SELECT TheCount = @TheCount
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

TheCount
-----------
123

(1 row(s) affected)

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 dbo.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)