Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Returning an IN clause from a CASE statement

    Hi all,

    I am passing in a variable into a CASE statement. Based on the value, I want to return a set of values in an IN clause. Here is an example:

    (The where clause will use a field called 'Location')

    Code:
    DECLARE @strTest as Varchar(50)
    SET @strTest = 'HI'
    
    SELECT * FROM [SomeTable]
    WHERE 
        CASE @strTest
            WHEN 'HI' THEN Location IN('1', '2', '3')
        END
    ORDER BY Location
    Is this possible to do?

    Sanctos

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Dynamic Sql will allow you to do this.

    DECLARE @strTest as Varchar(50)
    declare @whereclause as varchar(300)
    declare @sql as varchar(8000)

    SET @strTest = 'HI'

    set @whereclause = (CASE @strTest
    WHEN 'HI' THEN '(''a'', ''b'')' --- text values
    WHEN 'HI' THEN '(100, 200)' --- numeric values
    END)

    set @SQL = 'SELECT * FROM [tablename] WHERE [columnname] in ' + @whereClause
    exec (@sql)

Posting Permissions

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