SQL查询将nvarchar数据类型转换为int在存储过程中出现错误

Question

我对此有一个问题，我不知道为什么当我在存储过程中使用这个时，我一直得到这个错误。

转换nvarchar值时转换失败'3,25,26,27,28,33,34'到数据类型int

这是我使用的查询。

@idList nvarchar(max)
@idList='3,25,26,27,28,33,34'
update tSpecScaleValidation set LastTriggeredTime=getdate() where id in (@idList)

但是，当我不使用存储过程更新时，它工作得很好。

Answer 1

问题是变量@idList='3,25,26,27,28,33,34'作为整体被转换为整型。它不像你想的那样被转化了。你不能这样直接做。解决方法是使用动态查询:

declare @sql nvarchar(2000)
set @sql = 'udate tSpecScaleValidation set LastTriggeredTime=getdate() where id in (' + @idList + ')'
exec(@sql)

小心sql注入!坏消息是，您不能参数化此查询，因此，如果您从客户端传递带有变量@idList的一些数据，请小心。

Answer 2

因为@idList是NVARCHAR, WHERE子句被求值为id = '3,25,26,27,28,33,34'。由于id是INT，并且具有比NVARCHAR更高的数据类型优先级，因此'3,25,26,27,28,33,34'被转换为INT，从而导致错误:

转换nvarchar值时转换失败'3,25,26,27,28,33,34'到数据类型int

另一个选择是使用动态sql:

declare @sql nvarchar(max) 
set @sql= N'update tSpecScaleValidation set LastTriggeredTime = getdate() where id in (' + @idList + ');'
exec(@sql)

或者使用CSV拆分器。以下摘自Aaron Bertrand的文章:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

并将其用于UPDATE语句

update tSpecScaleValidation 
    set LastTriggeredTime=getdate() 
where id in (
    select item
    from dbo.SplitStrings_XML(@idList, ',')
)