Handling the data in an IN clause, with SQL parameters?

Question

We all know that prepared statements are one of the best way of fending of SQL injection attacks. What is the best way of creating a prepared statement with an "IN" clause. Is there an easy way to do this with an unspecified number of values? Take the following query for example.

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (1,2,3)

Currently I'm using a loop over my possible values to build up a string such as.

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (@IDVAL_1,@IDVAL_2,@IDVAL_3)

Is it possible to use just pass an array as the value of the query paramter and use a query as follows?

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (@IDArray)

In case it's important I'm working with SQL Server 2000, in VB.Net

Answer 1

Here you go - first create the following function...

Create Function [dbo].[SeparateValues]
(
    @data VARCHAR(MAX),
    @delimiter VARCHAR(10) 
) 
    RETURNS @tbldata TABLE(col VARCHAR(10))
As
Begin
    DECLARE @pos INT
    DECLARE @prevpos INT

    SET @pos = 1 
    SET @prevpos = 0

    WHILE @pos > 0 
        BEGIN

        SET @pos = CHARINDEX(@delimiter, @data, @prevpos+1)

        if @pos > 0 
        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, @pos-@prevpos-1))))

        else

        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, len(@data)-@prevpos))))

        SET @prevpos = @pos 
    End

    RETURN

END

then use the following...

Declare @CommaSeparated varchar(50)
Set @CommaSeparated = '112,112,122'
SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (select col FROM [SeparateValues](@CommaSeparated, ','))

I think sql server 2008 will allow table functions.

UPDATE

You'll squeeze some extra performance using the following syntax...

SELECT ID,Column1,Column2 FROM MyTable
Cross Apply [SeparateValues](@CommaSeparated, ',') s
Where MyTable.id = s.col

Because the previous syntax causes SQL Server to run an extra "Sort" command using the "IN" clause. Plus - in my opinion it looks nicer :D!