How to parameterize the set for an IN statement using inline SQL?

Question

Possible Duplicate:
Parameterizing a SQL IN clause?

Hi, I have a query looks like this:

SELECT 
    CompanyId 
FROM 
    CompanyTable 
WHERE 
    CompanyName IN ('Subway', 'A & W', 'Pizzahut')

Is there any way I can use sql parameters for the names list?

This is not a stored proc (which I prefer but can't use in this project). When I say 'parameter', I mean parameter in the parametrized inline sql.

I use MS Enterprise Library so my parametrized inline sql looks like this:

string sql = "SELECT * FROM Company WHERE CompanyID = @companyId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand(sql);
db.AddInParameter(dbCommand, "companyId", DbType.Int32, 123);
...

It is pretty straightforward for simple cases like above. But when it comes to something like

SELECT 
    CompanyId 
FROM 
    CompanyTable 
WHERE 
    CompanyName IN ('Subway','A & W','Pizzahut').

I have no idea how to use parameters here.

Answer 1

There are a few routes that you can take

• The "classic" pass in a delimited string parameter and use a user defined table-valued function in the database to turn the string into a table. Then you can join on that table to filter.

Something like (this will return a table of INT datatypes, just change the code slightly for VARCHAR

CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
    DECLARE     @index INT,
                @start_index INT,
                @id INT

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN

                SELECT @id = CAST(SUBSTRING(@list, @start_index, 
                        @index - @start_index ) AS INT)
                INSERT @list_table ([id]) VALUES (@id)
                SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, 
            @index - @start_index ) AS INT)
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

• You can use a loop in your .NET code to build the IN clause. Remember however that you are limited to 256 parameters (IIRC).

Something like this (a la this answer)

string[] tags = new string[] { "Subway","A & W","Pizzahut" };
string cmdText = 
    "SELECT CompanyId FROM CompanyTable WHERE CompanyName IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

• Depending on the version of SQL, could use an XML DataType Parameter (SQL Server 2005 onwards) to pass multiple parameters or a Table-Valued Parameter (SQL Server 2008 onwards)

Answer 2

Is this a stored procedure?

By Parameters, do you mean the stored procedure parameters?

When you say that you want to use the parameters for the names list, do you mean that you want to use one single parameter that is the entire list, or that you want to use one parameter for each string?

Assuming that this is a stored procedure, and that you mean to use its parameters, and that you want to use a single parameter (varchar) that is the entire list (ex: "'Subway', 'A & W', .....")

Here is what you can do: Look at my answer to this other question today: SQL - using a variable for an IN clause

Answer 3

In SSRS you can write IN (@list), but in regular T-SQL, you can't.

But you can pass a table-valued parameter into a proc in 2008, so you can easily handle it that way (and use a join instead too if you prefer).