Post reply

Multiple values in a CASE

  • April 6, 2009 at 8:25 am

    #130359

    Hi,

    I'm need of returning multiple values in a CASE statement.

    ex:

    SELECT * FROM table WHERE field IN ( CASE @somevar WHEN '111' THEN (111,222,333) ELSE @somevar END)

    the below one works

    SELECT * FROM table WHERE field IN ( CASE @somevar WHEN '111' THEN '222' ELSE @somevar END)

    but I want multiple values to be returned in the way shown above or something like this,

    SELECT * FROM table WHERE field IN ( CASE @somevar WHEN '111' THEN select value from table2 ELSE @somevar END)

    please let me know, if any queries.

    thanks in advance

  • April 6, 2009 at 9:50 am

    #972667

    Does either of the following queries do what you require?

    SELECT * FROM table

    WHERE (@somevar <> '111' AND field = CONVERT(int, @somevar))

    OR (@somevar = '111' AND field IN (111, 222, 333))

    SELECT * FROM table

    WHERE (@somevar <> '111' AND field = CONVERT(int, @somevar))

    OR (@somevar = '111' AND field IN (SELECT value FROM table2))

    I'm not sure what are the data types of your columns, so you will probably have to tweak the query to suit your database structure.

  • April 6, 2009 at 10:07 am

    #972685

    fantastic, Smith... I didn't think of this!!! I'll work out on my requirement now...

    Thanks for your help

  • April 6, 2009 at 12:44 pm

    #972817

    Although it looks as if you have an answer for your query now, be advised that case is an expression, and as such can only return a single value. In the future, if you are looking for multiple values to be returned, you are probably going to need a table valued function.

    Also, I just got a reminder about the necessity of testing when trying to use IN for a WHERE clause. If you find that your query is producing an unwanted table scan, you might want to look at the example Paul White posted here.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply