Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    2

    Question Unanswered: Dynamic 'IN' statement

    I'm looking to do something like the following:

    SELECT *
    FROM states
    WHERE abbr IN ('CA','OR','WA')

    However I want to retrieve the info by using a variable like so:

    DECLARE @MyList varchar(100)
    SET @MyList = 'CA,OR,WA'

    SELECT *
    FROM states
    WHERE abbr IN (@MyList)

    This of course does not work! I am wondering how I might achieve my goal of defining my IN statement using a variable.

    Any help is appreciated.
    CSD

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The set @mylist = 'CA,OR,WA' will not work. You have to store the text values individually. Try the following:

    declare @a varchar(100)
    set @a = '''CA''' + ',' + '''OR'''

    exec ('select * from states where abbr in (' + @a + ')')

  3. #3
    Join Date
    Aug 2002
    Posts
    2
    Thanks for the suggestion. I was hoping to avoid using an EXEC statement, but that looks like the only way to do it. If anyone has any thoughts on how to solve the problem without an EXEC let me know.

    Thanks
    CSD

  4. #4
    Join Date
    Sep 2002
    Posts
    3
    You can also do something like:

    declare @MyList nvarchar(100)
    declare @SQLStmt nchar(400)

    /* As you can see the trick is in getting the single quote character in the picture which you can do by adding char(39) as starting and closing of the state abbreviation.
    */

    set @MyList = char(39) + 'CA' + char(39) + ',' + char(39) + 'OR' + char(39) + ',' + char(39) + 'WA' + char(39)

    set @SQLStmt = 'SELECT * FROM states WHERE abbr in (' + rtrim(@MyList) + ')

    exec sp_executesql @SQLStmt

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Acctualy the single quote part is easy...

    Code:
    --------------------------------------------------------------------------------------------------------------------------------
    declare @MyList nvarchar(100)
    declare @SQLStmt nchar(400)

    /*
    ** As you can see the trick is in getting the single quote character in the picture which you can do by adding char(39) as starting and closing of the state abbreviation.
    */

    set @MyList = '''CA'',''OR'',''WA'''
    set @SQLStmt = 'SELECT * FROM states WHERE abbr in (' + @MyList + ')'

    select @MyList
    select @SQLStmt
    --------------------------------------------------------------------------------------------------------------------------------
    csdpdx; You have two options here, dynamic SQL as has been pointed out or build a temp table, stuff the diffrent state abbreviations into the table and join on the table for the answer. IMHO I would go for the dynamic sql.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •