Using Output Parameters in Stored Procedures

There are two ways of returning data from a stored procedure to the program that calls, or executes, the stored procedure:

1. A return value indicates the value of the stored procedure as a whole. RETURN is discussed in the previous lesson.
2. Output parameters are variables that pass data into a stored procedure for the purpose of the stored procedure modifying the values and passing them back to the calling program.

Specifying that a variable is used for returning values is as simple as specifying the OUTPUT keyword, like this:

CREATE PROCEDURE usp_GetLastName @EmployeeID int OUTPUT
AS
SELECT LastName
FROM employees 
WHERE EmployeeID = @EmployeeID

After you specify an output parameter, there is nothing else to do inside the stored procedure.
The rest is done in the program that calls the stored procedure, as you will see in the next lesson.

You can specify multiple parameters as being output parameters.

Click the Exercise link below to practice creating stored procedures with output parameters.
Using Output Parameters - Exercise