Variable expression "IN" in SQL

Question

Possible duplicates:
Multiple SQL Server (2008) parameter values
Pass ArrayList or String for SP for IN ()

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

SELECT * FROM Books WHERE Type IN (_expr_);

Where I want to _expr_be ('Humor', 'Thriller')one run, and maybe the ('Education')next, depending on the user's choice. How to change expression at runtime?

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

Answer 1

This is more complicated than you might think in SQL Server 2005 (in 2008, table options were more convenient)

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

Answer 2

It seems to me that I answered this question earlier ...

In any case, I have long used the following user-defined partition function:

Usage: dbo.Split ("@ParamName", ","), where the second parameter is the delimiter.

You can then attach this to the table since it returns a function of the table value with elementID and Element elements.

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

Answer 3

sql server 2005 , , :

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

Answer 4

another approach is to build a sql string and use execute to execute it. The row takes the form "INSERT ... SELECT" and inserts the results into a temporary table. Then you choose from 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