Case or Boolean Logic Question

• amns

  SSCrazy

  Points: 2628

  More actions

  August 25, 2011 at 8:26 am

  #247128

  Hi!

  I use 'case' statement in a stored procedure, like in the following example:

  SELECT * FROM EMPS

  WHERE

  EMP_NAME = CASE WHEN @SEARCH_NAME = 0 THEN EMP_NAME ELSE @EMP_NAME END

  AND

  EMP_NUMB = CASE WHEN @SEARCH_NUMB = 0 THEN EMP_NUMB ELSE @EMP_NUMB END

  Apparently the following code block produces the same results:

  SELECT * FROM EMPS

  WHERE

  (@SEARCH_NAME = 0 OR EMP_NAME = @EMP_NAME)

  AND

  (@SEARCH_NUMB = 0 OR EMP_NUMB = @EMP_NUMB)

  The second approach is faster, but will it always produce the same results?

  Sql Server will always process the comparisions (1º-(@SEARCH_NAME = 0),2º-(EMP_NAME = @EMP_NAME)) in the same order?

  Thanks!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 25, 2011 at 8:37 am

  #1373198

  http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• mtillman-921105

  SSCertifiable

  Points: 7156

  More actions

  August 25, 2011 at 8:40 am

  #1373201

  Yes, these two statements should always produce the same results.

  I'm not sure how the order matters, unless you are talking about how the OR's are processed. But you correctly have the OR statements in parentheses, which makes the logic work right.

  The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

• amns

  SSCrazy

  Points: 2628

  More actions

  August 25, 2011 at 9:25 am

  #1373252

  Thanks for your comments! 🙂

  GilaMonster, thanks for the link! Great article!