CODEWITHSUNDEEP
sqlsql-serversql-server-2005
gh9
gh9

Reputation: 10703

SQL Server Output Parameters

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

Upvotes: 4

Views: 1552

Answers (3)

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425613

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453598

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

Or, if you prefer

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

Upvotes: 4

Related Questions