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))
Select FirstName, LastName
From Person

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)
Select @LNameCount=  Count(*)
From Person
And how to execute it :

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)
RETURN @IntValue

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.

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s