Variable "IN" expression in SQL

Question

Possible Duplicates:
SQL Multiple Parameter Values
SQL Server (2008) Pass ArrayList or String to SP for IN()

I would like to SELECT some rows from a table that have certain values which are not known at the time a stored procedure is written. For example, searching for books of a particular type or types in a library database:

SELECT * FROM Books WHERE Type IN (_expr_);

Where I want _expr_ to be ('Humor', 'Thriller') one run, and maybe ('Education') the next, depending on the user's choices. How can I vary the expression at run-time?

Unfortunately, I still have a lot to learn about SQL in general and am not sure if I'm even asking a question that makes sense. I would appreciate any guidance!

The question makes perfect sense. First, what programming language/environment are you using? Also, is your query in a stored procedure or executed directly as a string in your program? — FrustratedWithFormsDesigner, Feb 11 '11 at 22:12
the problem you have is that you effectively want to create a collection/array of type x to use in the `IN` clause. Take a look at http://stackoverflow.com/questions/519769/sql-server-2008-pass-arraylist-or-string-to-sp-for-in/519793#519793 — Russ Cam, Feb 11 '11 at 22:13
My query is in a stored procedure, using MS SQL Server 2005. — WorkerThread, Feb 11 '11 at 22:14
See http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337792#337792 if you're using C# — nos, Feb 11 '11 at 22:16
Sorry for possibly asking a duplicate question :(. Sometimes it's hard to know what to search for when you're first learning. Thanks for pointing me in the right direction, though. — WorkerThread, Feb 11 '11 at 22:25

score 4 · Accepted Answer · answered Feb 11 '11 at 22:13

4

This is trickier than you might think in SQL Server 2005 (2008 has table valued parameters which makes it easier)

See http://www.sommarskog.se/arrays-in-sql-2005.html for a review of the methods.

answered Feb 11 '11 at 22:13

Martin Smith

438,706
87
741
845

score 2 · Answer 2 · answered Feb 11 '11 at 22:31

I feel like I've answered this question before...

anyway, I've long used the following user defined split function:

Usage: dbo.Split("@ParamName", ",") where the 2nd parameter is the separator.

You can then join this onto a table, as it returns a table value function with the elementID and Element.

CREATE FUNCTION [dbo].[Split]
(
@vcDelimitedString varchar(max),
@vcDelimiter varchar(100)
)
RETURNS @tblArray TABLE
   (
    ElementID smallint  IDENTITY(1,1), --Array index
    Element varchar(1000) --Array element contents
   )
AS
BEGIN
    DECLARE @siIndex smallint, @siStart smallint, @siDelSize smallint
    SET @siDelSize  = LEN(@vcDelimiter)
    --loop through source string and add elements to destination table array
    WHILE LEN(@vcDelimitedString) > 0
    BEGIN
        SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
        IF @siIndex = 0
        BEGIN
            INSERT INTO @tblArray VALUES(@vcDelimitedString)
            BREAK
        END
        ELSE
        BEGIN
            INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
            SET @siStart = @siIndex + @siDelSize
            SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
        END
    END
    RETURN
END

Joel Coehoorn · Answer 3 · 2011-02-11T23:54:03.863

1

What you do here for sql server 2005 and prior is put the user parameters in a table, and then select from the table:

select columns 
from books 
where type in 
    (
     select choices 
     from userchoices 
     where sessionkey= @sessionkey and userid= @userid
    )

edited Feb 11 '11 at 23:54

answered Feb 11 '11 at 22:17

Joel Coehoorn

399,467
113
570
794

score 1 · Answer 4 · answered Feb 11 '11 at 22:34

another approach, is to build a sql string and use execute to execute it. The string is of "INSERT...SELECT form" and inserts the results into a temporary table. Then you select from the temp.

declare @sql varchar(1000)
set @sql = 'INSERT INTO sometemptable  SELECT * FROM Books WHERE Type IN ('
set @sql = @sql + {code that builds a syntactically correct list}
set @sql = @sql + ')'
execute @s_sql
select * from sometemptable

Variable "IN" expression in SQL

4 Answers4

Linked