下列程序用输入参数值执行存储过程,并将存储过程的输出值保存到调用程序的局部变量 @ytd_sales_for_title 中。
-- Declare the variable to receive the output value of the procedure.
DECLARE @ytd_sales_for_title int
-- Execute the procedure with a title_id value
-- and save the output value in a variable.
EXECUTE get_sales_for_title "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT
-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title)
GO
下列示例显示有一个输入参数和一个输出参数的存储过程。存储过程中的第一个参数 @title 将接收由调用程序指定的输入值,而第二个参数 @ytd_sales 将向调用程序返回该值。SELECT 语句使用 @title 参数以获得正确的 ytd_sales 值,并将该值赋予 @ytd_sales 输出参数。
CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
RETURN
GO