Use Where In with Param

Question

How can I use a param for mulitple values in a "where in" query:

Select [..] where str in ('AB','BC')

Of course, this will not allow multiple values:

Select [..] where str in (@param)

Answer 1

You need to add each param as a value in the IN clause, e.g.

where str in (@param1, @param2, @param3)

Alternately, you could create a query manually and EXEC it (though that's fraught with peril as well).

EDIT: So, just to clarify, since you're receiving a string of comma-delimited values that you then want to use in an IN clause, you'll need to do what astander suggested and split your string into a table which you could then use in the clause.

Using one of the links astander mentioned you could do the following:

select [...]
where str in (select items from dbo.split(@ItemString))

(where your comma-delimited values are in @ItemString).

Answer 2

The alternative to adding each value as a separate param, if you are using SQL Server 2008, is to pass in a table valued parameter which would allow you to do something like:

SELECT...
FROM YourTable t
    JOIN @TableParam p ON t.str = p.str

If you're not using SQL 2008 and you actually want to pass all the values in as one CSV string, then the other common approach is to have a user defined function that splits the CSV string out and returns a TABLE which you can then join on, e.g.

SELECT ....
FROM YourTable t
    JOIN dbo.fnSplit(@param) s ON t.str = s.str

Answer 3

OK, so you need a split function

SQL Server split function

and

Split Function in Sql Server to break Comma-Separated Strings into Table

and

dbo.Split