SQL - Conversion failed when converting the varchar value to Int

0
votes

I am passing varchar values to a SQL function.

But I get this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '201,505,59,43,2202' to data type int.

Sample Code :

Declare @Fault Varchar(Max) = '201,505,59,43,2202'

Select * 
From EventMsg 
Where Event IN (Convert(Int, @Fault))

I tried both CAST & CONVERT functions...but same error.

2
sqlsql-server
The error message is fully comnprehensible. This string is not intepretable as an integer. How do you want it to get interpreted? - ppeterka
A single string value containing digits and commas is not the same as a sequence of multiple values. SQL Server does not look inside the single value and decide to split it into multiple values. - Damien_The_Unbeliever
Then is there any way to do it ? i want to pass all values at once in function. - Shaggy
SQL Server has one data type that actually supports multiple values natively. It's called... a table. So, if possible, switch to passing multiple values as a table-valued parameter - Damien_The_Unbeliever
possible duplicate of T-SQL stored procedure that accepts multiple Id values - Michael Edenfield

2 Answers

1
votes

If you are passing a comma separated list of integer values as a string and wanting to use each value then you will need to write a function similar to String.Split in the .NET world.

There are a ton of answers on Stack Overflow about this here are some

0
votes

Just another way is to declare table variable:

DECLARE @errorCodes AS TABLE (
    err INT
)
INSERT INTO @errorCodes (err) VALUES (1), (2), (3)

Select * From EventMsg Where Event IN (SELECT err FROM @errorCodes)