Specifying the Direction of a Parameter

Creating and Maintaining Databases

Creating and Maintaining Databases

Specifying the Direction of a Parameter

All procedure parameters can receive input values when the stored procedure is executed by the program that calls the stored procedure.

Examples

The following stored procedure, get_sales_for_title, uses an input parameter. The @title parameter in the stored procedure receives the input value of a title of a book specified by the calling program. The SELECT statement uses the @title parameter to obtain the correct ytd_sales value and displays the value.

CREATE PROCEDURE get_sales_for_title
@title varchar(80)   -- This is the input parameter.
AS

-- Get the sales for the specified title.
SELECT "YTD_SALES" = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO

If you specify the OUTPUT keyword for a parameter in the stored procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. The calling program must also use the OUTPUT keyword when executing the stored procedure to save the parameter's value in a variable that can be used in the calling program. For more information, see Returning Data Using OUTPUT Parameters.