存储过程默认参数与输出参数的顺序规范及异常原因咨询

我有一个从C#代码调用的旧存储过程，调用时出现异常，情况和Stack Overflow上某问题类似。

原存储过程参数定义：

@ReturnVal BIGINT OUTPUT,
@var       FLOAT = NULL

调用时未从C#代码传递这个带默认值的参数，后来把默认参数移到输出参数之后，问题解决：

@var       FLOAT = NULL,
@ReturnVal BIGINT OUTPUT

我想知道这个问题的原因，以及存储过程参数传递是否存在官方顺序规范。以下是我用Dapper的C#调用代码：

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);

问题原因 #

当调用存储过程时，如果不明确指定参数名称（比如你在EXEC语句里只传了@ReturnVal OUT，未明确对应所有参数的关系），SQL Server会按参数定义的顺序匹配传入的参数信息。

原参数顺序中，@ReturnVal是第一个参数（输出参数），@var是第二个参数（带默认值）。你调用时只提供了第一个参数的绑定信息，SQL Server会判定你给第一个参数赋值，同时认为第二个参数@var需要传入值——但你没传，即便它有默认值，这种顺序下的匹配逻辑会忽略默认值设定，触发参数缺失的异常。

把带默认值的@var移到输出参数前面后，调用时指定@ReturnVal OUT，SQL Server会正确识别@var使用默认值，@ReturnVal作为输出参数被正常绑定，不会再触发错误。

官方参数顺序规范 #

SQL Server官方对存储过程参数定义有明确要求：

• 所有带默认值的参数必须定义在无默认值的参数之后，输出参数属于无默认值参数范畴（因为需要绑定接收输出，无法依赖默认值）。
• 调用存储过程时：
  • 若按位置传递参数（不指定参数名称），必须严格遵循存储过程定义的参数顺序；
  • 若按名称传递参数，参数顺序不影响，但仍建议遵循官方定义顺序，避免匹配逻辑出现异常。

内容的提问来源于stack exchange，提问作者Vivek Nuna