What shuld be the default and output parameter order in the stored procedure?-CodePudding

I have an old stored procedure called from C# code, I found that it is throwing exceptions similar to what is mentioned in this question.

In my case, I have a default parameter and an output parameter like this:

@ReturnVal BIGINT OUTPUT,
@var       FLOAT = NULL

I am not passing the default parameter from the C# code while calling this stored procedure.

I am able to fix the issue by moving the default parameter after the output parameter, and it's working as expected.

@var       FLOAT = NULL,
@ReturnVal BIGINT OUTPUT

But I would like to know the reason behind it. Is there any standard for passing the parameter order? I have not found any official documentation about it.

Please find below the C# code, I am using Dapper.

string sql = "EXEC [mySP] @ReturnVal OUT";
DynamicParameters dynamicParamaters = new DynamicParameters(new { });
dynamicParamaters.Add(@ReturnVal, null, parameter.DbType, ParameterDirection.Output, 0);
int returnVal = _connection.ExecuteScalar<int>(sql, dynamicParams)

CodePudding user response：

Maybe take a step back, and try some things out in SSMS. Here's a simplified version of your stored proc:

CREATE PROC dbo.mySP (@var  FLOAT = NULL,  @ReturnVal BIGINT OUTPUT)
AS
    select @var as var
    set @ReturnVal = 2
GO

Your c# code is going to end up generating some TSQL similar to this:

DECLARE @ReturnVal BIGINT
EXEC dbo.MySP @ReturnVal OUT

This results in error: "The formal parameter "@var" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output"

This is because the parameter names have not been specified in the call, and the parameters are passed by position. So the "@ReturnVal OUT" is being passed to sqlserever as the 1st parameter to the stored proc (The stored proc's 1st parameter is actually @var).

Some possible options to avoid the problem:

Option 1, Pass parameters by position, and specify all parameters:

DECLARE @ReturnVal BIGINT
EXEC dbo.MySP default, @ReturnVal OUT   
select @ReturnVal as ReturnVal

Because the 2nd parameter defined in the stored proc (@ReturnVal) is mandatory to be passed, the keyword "default" is used to tell sqlserver to use the default parameter value defined in the stored proc for the 1st parameter.

(Note that your workaround of changing the order of parameters in the stored proc definition to:

CREATE PROC dbo.mySP (@ReturnVal BIGINT OUTPUT, @var  FLOAT = NULL)

worked, because the 2nd parameter @var is optional, so you don't have to pass it.
i.e.

EXEC dbo.MySP @ReturnVal OUT

and

EXEC dbo.MySP @ReturnVal OUT, default

are equivalent.)

Option 2, Pass parameters by name, and specify all parameters:

DECLARE @ReturnVal BIGINT
EXEC dbo.MySP @var = default,  @ReturnVal = @ReturnVal OUT   
select @ReturnVal

Note that in "@ReturnVal = @ReturnVal OUT", the left "@ReturnVal" is specifying the name of the stored proc's parameter, and right "@ReturnVal" is specifying the name of your local variable, which also happens to be called @ReturnVal in this case.

Option 3, Pass parameters by name. Leave out parameters with default values in the stored proc definition:

DECLARE @ReturnVal BIGINT
EXEC dbo.MySP @ReturnVal = @ReturnVal OUT   
select @ReturnVal

I'd recommend passing parameters by name, as it usually makes your code more robust. To see more clearly the TSQL that is being generated from your c# code, you could perform an SQL Server Profiler trace.