SQL Query conversion nvarchar to data type int got error in stored procedure

0
votes

I have a question on this, I am not sure why I keep getting this error when I use this in stored procedure.

Conversion failed when converting the nvarchar value '3,25,26,27,28,33,34' to data type int.

This is the query I used.

@idList nvarchar(max)

@idList='3,25,26,27,28,33,34'

update tSpecScaleValidation set LastTriggeredTime=getdate() where id in (@idList)

However, when I update without using stored proc, its working fine.

2
sql-serverstored-procedures

2 Answers

0
votes

Because @idList is NVARCHAR and the WHERE clause is evaluated as id = '3,25,26,27,28,33,34'. And since id is INT and has higher data type precedence than NVARCHAR, '3,25,26,27,28,33,34' gets converted to INT, which then resulted to the error:

Conversion failed when converting the nvarchar value '3,25,26,27,28,33,34' to data type int.

An alternative is to use dynamic sql:

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

exec(@sql)

Or use a CSV splitter. Here is one taken from Aaron Bertrand's article:

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

And use it on your UPDATE statement

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

Problem is that variable @idList='3,25,26,27,28,33,34' is converted to int as whole. It is not converted as you think. You can not do it like this directly. Workaround is to use dynamic query:

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

Beware of sql injection! Bad news that you can not parametrize this query, so be careful if you pass some data from client with variable @idList.