Conversion failed when converting the nvarchar value to data type int sql server-CodePudding

I have a table. There is data in this table and there is a checkbox next to each data. Multiple selection is possible. After the user makes a selection, the id numbers of the selected columns are come in the array. I convert the array to string and send it to the stored procedure and I run the following stored procedure:

Example value for @ResultsIds: 65, 66, 67, 68, 125

@ResultsIds nvarchar(250)

UPDATE MyTable SET [IsVerified] = 1 WHERE Id IN (@ResultsIds)

And I got this error:

Conversion failed when converting the nvarchar value '65, 66, 67, 68, 125' to data type int. Because [Id] column is int data type.

I tried CAST and CONVERT functions of SQL but it didn't work.

CodePudding user response：

SQL Server doesn't do that automatically. Assuming you're on a recent version, you can do this:

declare @ResultsIds nvarchar(250) = '65,66,67,68,125'

UPDATE MyTable
SET [IsVerified] = 1
WHERE Id IN (
   select [value] 
   from string_split(@ResultIDs, ',')
)

CodePudding user response：

declare @ResultsIds nvarchar(250)='65,66,67,68,125'
UPDATE MyTable SET [IsVerified] = 1 WHERE cast(Id as varchar) IN (@ResultsIds)