Tag Archives: Output

Returning Data

Returning Data from Strored Procedure
There are three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.

Result Sets
Like the procedure to return a list of all the people with a given last name

Create Procedure dbo.GetPeopleByLastName(@LastName varcgar(200))
As
Select FirstName, LastName
From Person
Where
LastName=@LastName

Using OUTPUT variables
To return a single value (or a couple of values) you can use output variables.

Create Procedure dbo.GetCountByLastName(@LastName varchar(200), @LNameCount int OUTPUT)
As
Select @LNameCount=  Count(*)
From Person
Where
LastName=@LastName
And how to execute it :
DECLARE @TheCount INT

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

Using Return

It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure.

Create Procedure TestReturn(@IntValue int)
As
RETURN @IntValue
Go

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.

Advertisements

Leave a comment

Filed under SQL