动态Pivot存储过程拼接INT类型参数出现转换失败如何解决？

问题原因 #

你遇到的转换报错是因为直接将INT类型的@WhereParam参数和NVARCHAR类型的SQL字符串做拼接运算，SQL Server的隐式类型转换规则会优先尝试把NVARCHAR字符串转为INT类型，因此触发转换失败错误。你之前尝试把拼接后的内容转INT的逻辑是反的，自然不会生效。

解决方案 #

方案1：显式把INT参数转为字符串后拼接 #

只需要把INT参数主动转成NVARCHAR类型再参与字符串拼接即可，修改后的存储过程代码如下：

CREATE PROCEDURE dbo.DynamicPivotTableInSql
  @ListToPivot    NVARCHAR(255),
  @WhereParam     INT
AS
BEGIN

DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = N'
SELECT * FROM (
  SELECT    
    [DateTime],[CountryCode],[Value]
  FROM MyDataTable
  WHERE SomeColumn = '+ CONVERT(NVARCHAR(10), @WhereParam) +'
) MyAlias
PIVOT (
  SUM([Value])
  FOR [CountryCode]
  IN ('+@ListToPivot+')
) AS PivotTable';

EXEC(@SqlStatement)
END

这个方案改动最小，直接解决当前报错问题。

方案2（更推荐）：用sp_executesql参数化执行动态SQL #

直接拼接参数存在SQL注入风险，用系统存储过程sp_executesql可以把值类型的参数做参数化传递，既避免类型转换问题，也更安全。修改后的代码如下：

CREATE PROCEDURE dbo.DynamicPivotTableInSql
  @ListToPivot    NVARCHAR(255),
  @WhereParam     INT
AS
BEGIN

DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = N'
SELECT * FROM (
  SELECT    
    [DateTime],[CountryCode],[Value]
  FROM MyDataTable
  WHERE SomeColumn = @InnerWhereParam
) MyAlias
PIVOT (
  SUM([Value])
  FOR [CountryCode]
  IN ('+@ListToPivot+')
) AS PivotTable';

-- 参数化执行动态SQL
EXEC sp_executesql 
  @SqlStatement,
  N'@InnerWhereParam INT', -- 声明动态SQL内部用到的参数
  @InnerWhereParam = @WhereParam -- 给内部参数传值
END

注意：@ListToPivot是要替换的列名列表，属于SQL结构的一部分，不能参数化，所以还是需要直接拼接。

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