Output Assigned to Position not Name of Stored Procedure Parameter 1

[SeanGriffin]

I was wondering if anyone could tell me why this happened.

I have a Stored Procedure in SQL 2000 where when I define it as follows it works the way I expect it to.

CREATE PROCEDURE [dbo].[addSequenceBinaryData]
@sequenceID int,
@testData image,
@fileName varchar(255),
@retval bigint OUTPUT, 
@ReplacedBy int = null,
@rowguid uniqueidentifier = null

However if I reorder the Parameters it always treats the fourth value as an output even though I move the output statment with the @retval parameter. Example:

CREATE PROCEDURE [dbo].[addSequenceBinaryData]
@sequenceID int,
@testData image,
@fileName varchar(255),
@ReplacedBy int = null,
@rowguid uniqueidentifier = null,
@retval bigint OUTPUT

@ReplacedBy is treated as an output and I get errors because I don't specify it as one when calling it.

Any Suggestions would be much appreciated.

-Sean

 

[SQLDenis]

AFAIK optional parameters have to be the last in the list

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/