SQL Server - Replacing Single Quotes and Using IN

Question

Hello,

I am passing a comma-delimited list of values into a stored procedure. I need to execute a query to see if the ID of an entity is in the comma-delimited list. Unfortunately, I think I do not understand something.

When I execute the following stored procedure:

exec dbo.myStoredProcedure @myFilter=N'1, 2, 3, 4'

I receive the following error:

"Conversion failed when converting the varchar value '1, 2, 3, 4' to data type int."

My stored procedure is fairly basic. It looks like this:

CREATE PROCEDURE [dbo].[myStoredProcedure]
    @myFilter nvarchar(512) = NULL
AS
SET NOCOUNT ON
BEGIN
    -- Remove the quote marks so the filter will work with the "IN" statement
    SELECT @myFilter = REPLACE(@myFilter, '''', '')

    -- Execute the query
    SELECT
     t.ID,
     t.Name  
    FROM
     MyTable t
    WHERE
     t.ID IN (@myFilter)
    ORDER BY
     t.Name
END

How do I use a parameter in a SQL statement as described above? Thank you!

Answer 1

You need to split the string and dump it into a temp table. Then you join against the temp table.

There are many examples of this, here is one at random.

http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

Answer 2

I would create a function that takes your comma delimited string and splits it and returns a single column table variable with each value in its own row. Select that column from the returned table in your IN statement.

Answer 3

I found a cute way of doing this - but it smells a bit.

declare @delimitedlist varchar(8000)
set @delimitedlist = '|1|2|33|11|3134|'

select * from mytable where @delimitedlist like '%|' + cast(id as varchar) + '|%'

So... this will return all records with an id equal to 1, 2, 33, 11, or 3134.

EDIT: I would also add that this is not vulnerable to SQL injection (whereas dynamic SQL relies on your whitelisting/blacklisting techniques to ensure it isn't vulnerable). It might have a performance hit on large sets of data, but it works and it's secure.

Answer 4

Absent a split function, something like this:

CREATE PROCEDURE [dbo].[myStoredProcedure]
    @myFilter varchar(512) = NULL -- don't use NVARCHAR for a list of INTs
AS
SET NOCOUNT ON
BEGIN
    SELECT
        t.ID,
        t.Name          
    FROM
        MyTable t
    WHERE
        CHARINDEX(','+CONVERT(VARCHAR,t.ID)+',',@myFilter) > 0
    ORDER BY
        t.Name
END

Performance will be poor. A table scan every time. Better to use a split function. See: http://www.sommarskog.se/arrays-in-sql.html

Answer 5

You could make function that takes your parameter, slipts it and returns table with all the numbers in it.

If your are working with lists or arrays in SQL Server, I recommend that you read Erland Sommarskogs wonderful stuff:

Arrays and Lists in SQL Server 2005

Answer 6

I have a couple of blog posts on this as well, with a lot of interesting followup comments and dialog:

http://is.gd/4wsnn

http://is.gd/4wsp3