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 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!

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

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

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

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.

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.

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

More on splitting lists

Processing list of integers