Dynamically creating the IN clause in a stored procedure [duplicate]

Question

Possible Duplicate:
Need help in dynamic query with IN Clause

I am using SQL server 2008 and this is the problem that I am facing. I have a table named Cars with a column Company. Now I have a stored procedure that looks something like this

CREATE PROCEDURE FindCars (@CompanyNames varchar(500))
AS
SELECT * FROM Cars WHERE Company IN (@CompanyNames)

I tried something like this and failed

DECLARE @CompanyNames varchar(500)
SET @CompanyNames = '''Ford'',''BMW'''

exec FindCars @CompanyNames

I dont get any rows returned. When I do the following

DECLARE @CompanyNames varchar(500)
SET @CompanyNames = '''Ford'',''BMW'''

Select @CompanyNames

I get the following result

'Ford','BMW'

and if I replace this value in the select statement inside the stored procedure, it works

SELECT * FROM Cars where Company in ('Ford','BMW')

Thus I think that the stored procedure seems to be treating 'Ford','BMW' as one string rather than an array. Could someone please help me with this. How do I dynamically construct the string/array required in the IN clause of the select statement inside the stored procedure.

Answer 1

You are right, you created one string, and that is being processed as a list of strings that contains one string. The commas are just characters in that one string. The only equivilent to an array in SQL Server is a table.

For example; WHERE x IN (SELECT y FROM z).

For this reason many people create a SPLIT_STRING() function that returns a table of items from a given comma delimitted string...

WHERE x IN (SELECT item FROM dbo.split_string(@input_string))

There are many ways to implement that split string. Some return strings, some cast to integers, some accept a second "delimiter" parameter, etc, etc. You can search the internet for SQL SERVER SPLIT STRING and get many results - Including here in StackOverflow.

An alternative is to use dynamic SQL; SQL that writes SQL.

SET @sql = 'SELECT * FROM x WHERE y IN (' + @input_string_list + ')'

SP_EXECEUTESQL @sql

(I recommend SP_EXECUTESQL over just EXEC because the former allows you to use parameterised queries, but the latter does not.)