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 to store the returned value. The execute statement must include the OUTPUT keyword as well, or the modifications won't be reflected in the calling batch's variable:

DECLARE @sales_up_to_today  int 
EXEC ytd_sales 'Life Without Fear', @sales_up_to_today OUTPUT
PRINT 'Sales this year until today''s date: ' +
      CONVERT(VARCHAR(10), @sales_up_to_today) + '.'

Sales this year until today's date: 111.

You can also pass the output parameter by name:

DECLARE @sales_up_to_today  int 
EXEC ytd_sales 'Life Without Fear',
      @ytd_sales = @sales_up_to_today OUTPUT
PRINT 'Sales this year until today''s date: ' +
      CONVERT(VARCHAR(10), @sales_up_to_today) + '.'

Note that when you pass an output parameter by name, the paramater name (@ytd_sales in this example) is listed on the left side of the expression, and the local variable (@sales_up_to_today), which will be set equal to the value of the output paramater, is on the right side of the expression. An output parameter can also serve as an input parameter.

OUTPUT parameters can also be passed back and captured in a client application using ADO, ODBC, OLE DB, and so on.