Sql Server output parameters

Question

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

Answer 1

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

Answer 2

Quoting the relevant portion of the TSQL reference from MSDN (about 2/3 of the way down the page): Emphasis mine

---

First, create the procedure:

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'titles_sum' AND type = 'P')
   DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles 
WHERE title LIKE @TITLE 
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO

Next, use the OUTPUT parameter with control-of-flow language.

Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.

The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @SUM = variable is used).

DECLARE @TOTALCOST money
EXECUTE titles_sum 'The%', @TOTALCOST OUTPUT
IF @TOTALCOST < 200 
BEGIN
   PRINT ' '
   PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
   SELECT 'The total cost of these titles is $' 
         + RTRIM(CAST(@TOTALCOST AS varchar(20)))

Here is the result set:

Title Name                                                               
------------------------------------------------------------------------ 
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.

All of these titles can be purchased for less than $200.