Sql-server – Sql Server output parameters

sql serverstored-procedures

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 decleration 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

Best Answer

There is nothing special about OUTPUT parameters when it comes to "named parameters" or "ordinal parameters". In SQL Server this terminology this applies to the EXEC call and how you specify parameters there: not direction

  • Ordinal = position must match and datatype must be compatible
  • Named = assign the local value to the stored proc parameter name. Only datatype must be compatible

This is ordinal

declare @bob as int, @rich as char(20)
--OK
exec test @bob output, @rich output
GO

declare @bob as int, @rich as char(20)
--Fail
exec test @rich output, @bob output

This is named

declare @bob as int, @rich as char(20)
--OK
exec test
      @ID = @bob output,
      @mark = @rich output
GO

declare @bob as int, @rich as char(20)
--OK
exec test
      @mark = @rich output,
      @ID = @bob output

If you had a stored proc with parameters with defaults ...

create procedure test2
  @ID as INT output
 ,@filler1 tinyint = 0 --has default
 ,@mark as char(20) output
 ,@filler2 tinyint = 0 --has default
as
     select @ID = 5,@mark='test'
go

.. then you need the DEFAULT keyword for ordinal parameters if you have subsequent mandatory parameters

exec test2 @bob output, DEFAULT, @rich output --don't need to do anything for @filler2

For named parameters, it can be DEFAULT keyword or ignored because order is irrelevant

exec test2
      @ID = @bob output,
      @mark = @rich output,
      @filler2 = DEFAULT

exec test2
      @mark = @rich output,
      @ID = @bob output

And the obligatory MSDN link

Related Question