locked
case statement inside a where clause with 'IN' operator RRS feed

  • Question

  • This is the code am trying to build and it fails

    SELECT * FROM MyTable 
    WHERE product_type IN (
    CASE WHEN @product='Mobile' THEN ('SAMSUNG', 'APPLE', 'NOKIA')
             WHEN @product='car' THEN ('honda', 'acura', 'toyota')
              WHEN @product='laptop' THEN ('dell', 'acer') end)

    How do I come up with a solution?

    Wednesday, August 15, 2012 2:35 PM

Answers

  • Try it as follow:

    SELECT * FROM MyTable  
    WHERE @product='Mobile' and product_type IN ('SAMSUNG', 'APPLE', 'NOKIA')
    or( @product='car' and product_type IN ('honda', 'acura', 'toyota') )
    or (@product='laptop' and product_type IN ('dell', 'acer'))


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Naomi N Sunday, August 19, 2012 12:47 PM
    Wednesday, August 15, 2012 2:57 PM

All replies

  • Try

    SELECT * FROM MyTable 
    WHERE @product='Mobile' and product_type IN ('SAMSUNG', 'APPLE', 'NOKIA')

    or @product='car' and product_type IN ('honda', 'acura', 'toyota')

    or @product='laptop' and product_type IN ('dell', 'acer')


    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Wednesday, August 15, 2012 2:45 PM
    • Marked as answer by penro Wednesday, August 15, 2012 3:19 PM
    • Unmarked as answer by Naomi N Sunday, August 19, 2012 12:47 PM
    Wednesday, August 15, 2012 2:45 PM
  • Hua , Thanks for the reply. I tried this appreoad. But the problem here is when I execute the querry giving the parameter @prodcust = mobile, it worked fine. But when @prodcut = car, it doesnt give any result set and just says "command executes successfully" ??

    Thanks

    Pen

    Wednesday, August 15, 2012 2:52 PM
  • Try it as follow:

    SELECT * FROM MyTable  
    WHERE @product='Mobile' and product_type IN ('SAMSUNG', 'APPLE', 'NOKIA')
    or( @product='car' and product_type IN ('honda', 'acura', 'toyota') )
    or (@product='laptop' and product_type IN ('dell', 'acer'))


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Naomi N Sunday, August 19, 2012 12:47 PM
    Wednesday, August 15, 2012 2:57 PM
  • It doesnt work either.
    Wednesday, August 15, 2012 3:04 PM
  • Hua , Thanks for the reply. I tried this appreoad. But the problem here is when I execute the querry giving the parameter @prodcust = mobile, it worked fine. But when @prodcut = car, it doesnt give any result set and just says "command executes successfully" ??

    Thanks

    Pen


    If you get no result, it can be due to the value of product_type. You can just remove 'product_type in' clause for car to see what value product_type is.

    Many Thanks & Best Regards, Hua Min

    Wednesday, August 15, 2012 3:11 PM
  • Thank You Hua and Farooq . I got it
    Wednesday, August 15, 2012 3:33 PM