Using CASE statement in a WHERE clause

Using CASE statement in a WHERE clause

• jmsides

  Yes, you just need to remember that CASE is not a statement -- it is an expression.  It outputs a scalar value, and does not control flow. 

   

  So to use it in a WHERE clause:

   

   

  SELECT *

  FROM tablex

  WHERE

      dm_marque = 

          CASE

              WHEN abc IS NOT NULL THEN 'foo'

              ELSE 'bar'

          END

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Karen Longstaff@discussions..microsoft.com> wrote in message news:4520433d-3f4f-4051-928c-6c777ff303c7@discussions.microsoft.com...

  Is it possible to use CASE within a WHERE

  I have a query which is something like this, but it returns an error:

  SELECT * FROM tablex

  WHERE

  CASE WHEN 'sexec' IS NOT NULL THEN

  dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE

  dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END

  GROUP BY dm_marque

  In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.

  Many thanks

  Karen

  
  

• Duke Ames

  Thanks, I'll have a go!

  Karen

  
  
• Using CASE statement in a WHERE clause