WHERE IN with a local variable

Question

Possible Duplicate:
Parameterizing an SQL IN clause?
SQL:Casting a String to IDS with IN clause

I want to use a declare local variable in a WHERE IN clause

Something like this:

TABLE XYZ

COL1 COL2
1    A
2    B
3    C
4    D
5    E

RESULT

1 A
2 B
5 E

QUERY

DECLARE @VAR VARCHAR(MAX)
SET @VAR = '1,2,5'
SELECT * FROM XYZ WHERE COL1 IN @VAR

How do I do this?

Note : I cant have a server function

I can only create primitive value (by code) and use it with my query. I search a way that I will only change my var and not the query itself.

my code look like:

list (dbparameter)
mylist.add ('@var','1,2,5')

commandsql.returnresult(myQueryInString,mylist)

I want to

tank you bluefeet, I still have issue with forum template – forX Apr 18 '12 at 18:51 — forX, Apr 18 '12 at 18:51
I cant create a function in the database – forX Apr 18 '12 at 18:52 — forX, Apr 18 '12 at 18:52

Quassnoi · Answer 1 · 2012-04-18T19:02:05.920

DECLARE @var TABLE
        (
        value INT
        )

INSERT
INTO    @var
VALUES
        (1), (3), (5)

/* You would need three separate inserts in 2005 */

SELECT  *
FROM    xyz
WHERE   col1 IN
        (
        SELECT  value
        FROM    @var
        )

You can also write a table-valued function which splits a CSV, but if your client library supports passing table variables, this is a preferred option.

You can find the function definition in the Erlang Sommarskog's article (search for simple_intlist_to_tbl). Declare it and call like this:

DECLARE @var VARCHAR(100) = '1,3,5'

SELECT  *
FROM    xyz
WHERE   col1 IN
        (
        SELECT  number
        FROM    simple_intlist_to_tbl(@var)
        )

If your query is more complex than that, you would want to materialize this list first:

DECLARE @var VARCHAR(100) = '1,3,5'

DECLARE @tvar TABLE
        ( 
        number INT
        )

INSERT
INTO    @tvar
SELECT  number
FROM    simple_intlist_to_tbl(@var)

SELECT  *
FROM    xyz, ... /* Other complex joins */
WHERE   col1 IN
        (
        SELECT  number
        FROM    @tvar
        )

and the var should be set in code, and I just have primitif type. So I will not able to push X number of value in @var. — forX, Apr 18 '12 at 18:56

score 0 · Answer 2 · answered Apr 18 '12 at 19:31

0

You need a function which splits your list into a data set (use this from Jeff Moden it works and is very fast) and then just use the IN clause on your desired column(s).

answered Apr 18 '12 at 19:31

Vince Pergolizzi

6,396
3
19
16

I CANT create a function on the server – forX Apr 18 '12 at 19:33
You can run the code in-line within your query it will work just the same. Scroll to the very bottom of the article if you don't want to read through all of it. – Vince Pergolizzi Apr 18 '12 at 19:35

WHERE IN with a local variable

2 Answers2