0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shaggy
  • 5,422
  • 28
  • 98
  • 163
  • 1
    The error message is fully comnprehensible. This string is not intepretable as an integer. How do you want it to get interpreted? – ppeterka Mar 06 '13 at 09:40
  • 2
    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 Mar 06 '13 at 09:41
  • Then is there any way to do it ? i want to pass all values at once in function. – Shaggy Mar 06 '13 at 09:41
  • 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](http://msdn.microsoft.com/en-gb/library/bb510489.aspx) – Damien_The_Unbeliever Mar 06 '13 at 09:44
  • possible duplicate of [T-SQL stored procedure that accepts multiple Id values](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) – Michael Edenfield Mar 06 '13 at 18:10

2 Answers2

1

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

Community
  • 1
  • 1
Kane
  • 16,471
  • 11
  • 61
  • 86
0

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)
Sergio
  • 6,900
  • 5
  • 31
  • 55