SQL Server Output Parameters

Question

Environment is SQL Server 2005

Is there a way to use named parameters when getting the output parameters of a stored proc? Currently, my knowledge of output parameters is very limited. It looks like I have to use them in order of their declaration in the stored proc. i.e., if I did exec test @rich output,@bob output the call would blow up. How can I have the order be arbitary? Thank you

create procedure test
  @ID as INT output
 ,@mark as char(20) output
as

  select @ID = 5,@mark='test'
go
declare @bob as int
declare @rich as char(20)
exec test @bob output, @rich output
select @bob,@rich

Martin Smith · Accepted Answer · 2011-04-28 14:02:00Z

4

EXEC test @ID = @bob OUTPUT, @mark = @rich OUTPUT

Or, if you prefer

EXEC test @mark = @rich OUTPUT, @ID = @bob OUTPUT

answered Apr 28, 2011 at 14:02

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quassnoi · Accepted Answer · 2011-04-28 14:02:37Z

1

declare @bob as int
declare @rich as char(20)
exec test
        @mark = @rich output,
        @id = @bob output
select @bob,@rich

answered Apr 28, 2011 at 14:02

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

1 Comment

Philip Kelley Over a year ago

The three answers I currently see are equivalent, but putting each parameter on a separate line makes it a clearer answer.

SQLMenace · Accepted Answer · 2011-04-28 14:03:23Z

0

instead of positional use named parameters

declare @bob as int
declare @rich as char(20)


exec test @ID = @bob output, @mark = @rich output
select @bob,@rich

exec test  @mark = @rich OUTPUT,@ID = @bob output
select @bob,@rich

answered Apr 28, 2011 at 14:03

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Collectives™ on Stack Overflow

SQL Server Output Parameters

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related