SQL Server 2016使用sp_executesql传入参出参报错如何解决

1. 表名变量无法识别问题 #

报错原因 #

SQL Server 的参数化查询仅支持传入值类型参数，表名、列名、架构名这类属于标识符，无法通过参数化传递，必须直接拼接进动态SQL语句中。你要求保留的@asOfDate日期参数可以用参数化写法，不需要拼接，同时规避注入风险和格式问题。
另外原代码存在两处语法错误：DECLARE拼写错误为DELCARE、sp_executesql的参数列表之间漏了逗号。

修复代码 #

DECLARE @fullTableName varchar(128);
-- 用QUOTENAME包裹标识符，避免特殊字符或注入风险
SET @fullTableName = CONCAT(QUOTENAME(@schema), '.', QUOTENAME(@TableName));

DECLARE @recordExists int;
DECLARE @recordExistsQuery nvarchar(max);
SET @recordExistsQuery = N'SELECT @recordExists=COUNT(*) FROM ' + @fullTableName + N' WHERE validFrom <= CAST(@asOfDate as datetime)';

EXEC sp_executesql 
    @recordExistsQuery,
    N'@recordExists INT OUT, @asOfdate datetime',
    @recordExists = @recordExists OUT,
    @asOfDate = @asOfDate;

2. 参数输出属性不匹配问题 #

报错原因 #

sp_executesql的传参顺序必须和定义的参数列表顺序完全一致，或者显式指定参数名。你定义的参数列表顺序是@asOfDate datetime, @recordExists INT OUT，但调用时只传了@recordExistsOut，系统会默认把这个参数匹配给第一个参数@asOfDate，而@asOfDate没有声明为OUTPUT，就触发了属性不匹配的报错。

修复方法 #

传参时按顺序传入所有参数，或者显式指定参数名，和上一个问题的修复代码一致即可。 #

3. 日期字符串转换失败问题 #

报错原因 #

你把datetime类型的@asOfDate直接和字符串拼接时，SQL Server会隐式将datetime类型转换为字符串，转换格式受数据库默认配置影响，大概率会出现格式不兼容的问题；如果@asOfDate为NULL，整个动态SQL拼接结果都会变成NULL，也会触发异常。

修复方法 #

不要拼接日期参数，回到参数化传递@asOfDate的方案，完全避免字符串转换问题，修复代码和第一个问题的代码一致。 #

内容的提问来源于stack exchange，提问作者Brian Karabinchak