Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Using Output Parameters

If a calling batch passes a variable as a parameter to a stored procedure, and that parameter is modified inside the procedure, the modifications will not be passed to the calling batch unless you specify the OUTPUT keyword for the parameter when creating and executing the stored procedure.

If you want a procedure to be able to pass parameters out from the procedure, use the keyword OUT[PUT] when creating and calling the procedure. The following example accepts two parameters, one of which is used as an OUTPUT parameter:

CREATE PROC ytd_sales 
@title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
   FROM titles
   WHERE title = @title
RETURN

The calling batch (or stored procedure) needs to declare a variable ...