locked
Case statement in where clause RRS feed

  • Question

  • Hello:

    I need to write case statement in where clause to filter the result set, I tried following query but I am not getting proper result set.

    Where

    CI

    .GLCreditAccount = Case when @GLCreditAccount = Null then CI.GLCreditAccount is null

       /*when @GLCreditAccount value is null, it's not giving any result. I have 4 rows though with NULL.  if I tried CI.GLCreditAccount is null then getting result)*/

                                          when @GLCreditAccount = 'All' then CI.GLCreditAccount

    /*when @GLCreditAccount value is ALL, it's giving result except 4 null rows, I need all rows.) */

                                         else @GLCreditAccount

    End Thanks for helping.


    kmp

    Thursday, April 19, 2012 7:44 PM

Answers

All replies

  • You need to write separate statements or

    where 1 = case when @GLCreditAccount IS NULL and CI.GLCreditAccount IS NULL then 1
    
                          when @GlCreditAccount = 'All' then 1
                          when CI.CreditAccount = @GLCreditAccount then 1 else 0 end

    However, dynamic SQL or separate statements for each case may be better (depending on your SQL Server version).

    Check these blog posts: 

    Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
    Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
    Catch All Queries - short blog by Gail Shaw

    Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

    Option recompile Option recompile discussion thread


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, April 19, 2012 7:51 PM
    • Proposed as answer by Chuck Pedretti Thursday, April 19, 2012 7:51 PM
    • Marked as answer by KevinPat Thursday, April 19, 2012 7:55 PM
    Thursday, April 19, 2012 7:51 PM
  • Thank you very much, I am getting required result set.


    kmp

    • Marked as answer by KevinPat Thursday, April 19, 2012 7:55 PM
    • Unmarked as answer by Naomi N Thursday, April 19, 2012 7:55 PM
    Thursday, April 19, 2012 7:55 PM
  • Naomi:

    How can I learn SQL like you? I want to be an expert in writing SQL!!!


    kmp

    Thursday, April 19, 2012 8:15 PM
  • Funny. I wish to understand SQL Server as Jonathan K or Dmitry K or people I see in Database Engine forum.

    To write sql queries I personally just like to hang up in forums (this one mostly). But there are great books - if you find a book by Itzik Ben-Gan you'll learn it.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, April 19, 2012 8:20 PM
    Thursday, April 19, 2012 8:19 PM