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.
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
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(*)
And how to execute it :
DECLARE @TheCount INT
@LastName = ‘Alexander’,
@LastNameCount = @TheCount OUTPUT
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)
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.