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

Question

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"

Answer 1

If you are using sql server 2016 and above then use STRING_SPLIT

declare @intArray varchar(200)
Set @intArray = '3,4,6,7'

SELECT *
FROM tbl_Employee
WHERE employeeID IN (select * from STRING_SPLIT(@intArray, ','))

Answer 2

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!

Answer 3

You're trying to convert not only a string to int, but several ints into one. Do you expect that your SELECT will return all employee's with and ID listed in the array?

I realize that you wanted to do this without a function. However, this is how I currently do it and it works great. Take what you will from my answer.

This code uses a while loop which could likely be improved to a recursive CTE if you are in SQL 2005/2008. You can use the output of the function as a table that you INNER JOIN to which will allow you to filter very quickly.

/*
************************************************************************************************************************
    Name:           ConvertDelimitedListIntoTable
    Description:    Converts a list of delimited values into a table for use like a dynamic IN statment

    Modification History
    Date        Author          Description
    ==========  ============    ====================================
    2009-01-31  B. Williams     Initial Creation

************************************************************************************************************************
*/
ALTER FUNCTION [dbo].[ConvertDelimitedListIntoTable] (
     @list NVARCHAR(MAX) ,@delimiter CHAR(1) )
RETURNS @table TABLE ( 
     item VARCHAR(255) NOT NULL )
AS 
    BEGIN
        DECLARE @pos INT ,@nextpos INT ,@valuelen INT

        SELECT  @pos = 0 ,@nextpos = 1

        WHILE @nextpos > 0 
            BEGIN
                SELECT  @nextpos = CHARINDEX(@delimiter,@list,@pos + 1)
                SELECT  @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
                                         ELSE LEN(@list) + 1
                                    END - @pos - 1
                INSERT  @table ( item )
                VALUES  ( CONVERT(INT,SUBSTRING(@list,@pos + 1,@valuelen)) )
                SELECT  @pos = @nextpos

            END

        DELETE  FROM @table
        WHERE   item = ''

        RETURN 
    END

Use:

DECLARE @intArray varchar(200)

SELECT *
FROM  tbl_Employee e
      INNER JOIN dbo.ConvertDelimitedListIntoTable(@intArray,',') arr
                 ON e.EmployeeID = arr.Item

---

There may also be a quick way to do this with a tally table.