Microsoft® SQL Server 2012 Unleashed

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 are not passed to the calling batch unless you specify the OUTPUT keyword for the parameter when executing the stored procedure.

If you want a procedure to be able to pass parameter values out from the procedure, you need to use the keyword OUTPUT when creating the procedure. The following example shows a stored procedure that accepts two parameters, one of which is used as an output parameter:

Click here to view code image

IF EXISTS ( SELECT * FROM sys.procedures               WHERE schema_id= schema_id('dbo')                 AND name = N'product_sales')   DROP PROCEDURE dbo.product_sales ...