» Scimore News Blog

Support stored procedure OUTPUT parameters

2010-05-19 - Marius Slyzius

Since version 3...1933 stored procedures can return output parameters. Use optional OUTPUT keyword to indicate that the parameter is a return parameter. The OUTPUT variable must be defined during procedure creation as well as during use of the parameter.

Example using output parameters:

-- create procedures
create procedure _output_inner_test1 ( @myparam1 int,@myparam2 int output,@myparam3 int output)
as
begin
    set @myparam2 = @myparam1 + 100
    set @myparam3 = @myparam1 + 1000
end;

create procedure output_test1
as
begin
    declare @in int = 100, @p1 int,@p2 int
    execute _output_inner_test1 @in,@p1 OUTPUT, @p2 OUTPUT
    if @p1 <> 200   RAISE EXCEPTION 'incorrect output'
    if @p2 <> 1100 RAISE EXCEPTION 'incorrect output'
    select @p1,@p2
end;

--run test
exec output_test1