Sql Server - Replacing Single Quotes And Using In

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 thin

Solution 1:

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

Solution 2:

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

Solution 3:

Absent a split function, something like this:

CREATEPROCEDURE [dbo].[myStoredProcedure]
    @myFiltervarchar(512) =NULL-- don't use NVARCHAR for a list of INTsASSET NOCOUNT ONBEGINSELECT
        t.ID,
        t.Name          
    FROM
        MyTable t
    WHERE
        CHARINDEX(','+CONVERT(VARCHAR,t.ID)+',',@myFilter) >0ORDERBY
        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

Solution 4:

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.

Solution 5:

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

declare@delimitedlistvarchar(8000)
set@delimitedlist='|1|2|33|11|3134|'select*from mytable where@delimitedlistlike'%|'+cast(id asvarchar) +'|%'

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.