Specifying the Parameter’s Direction

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 parameter’s current value 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.