Search code examples
t-sql

t-sql Convert comma separated string into int, without using user created function

I'm passing a list of int's (comma separated)

ie. 1, 2, 3, 4

to my sp. But I'm getting an error because the list is a string, and I'm comparing to an int field. Is there a way for me to convert the list to int, without using a user created function?

Note: employeeID is INT

declare @intArray varchar(200)

SELECT *
FROM tbl_Employee
WHERE employeeID IN ( @intArray )

The error is "Cannot convert type varchar to int"

Solution

  • You don't want to cast that list into an int, but into a list of ints.

    There is no cast operator or function for that, but you can use dynamic SQL to get around it.

    Basically you write

    EXECUTE('SELECT * FROM tbl_Employee WHERE employeeID IN ('+@intArray+')')

    Be aware of SQL injection attacks though!