“CASE” statement within “WHERE” clause in SQL Server 2008

by

First off, the CASE statement must be part of the expression, not the expression itself.

In other words, you can have:

WHERE co.DTEntered = CASE 
                          WHEN LEN('blah') = 0 
                               THEN co.DTEntered 
                          ELSE '2011-01-01' 
                     END

But it won’t work the way you have written them eg:

WHERE 
    CASE LEN('TestPerson')
        WHEN 0 THEN co.personentered  = co.personentered
   ELSE co.personentered LIKE '%TestPerson'
    END

You may have better luck using combined OR statements like this:

WHERE (
        (LEN('TestPerson') = 0 
             AND co.personentered = co.personentered
        ) 
        OR 
        (LEN('TestPerson') <> 0 
             AND co.personentered LIKE '%TestPerson')
      )

Although, either way I’m not sure how great of a query plan you’ll get. These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

More Related Contents:

  1. How do I perform an IF…THEN in an SQL SELECT?
  2. Can I use CASE statement in a JOIN condition?
  3. OR is not supported with CASE Statement in SQL Server
  4. Case in Select Statement
  5. SELECT query with CASE condition and SUM()
  6. Dynamic order direction
  7. Is it possible to write single query on two table which are not connected to each other?
  8. select yesterday date in where condition
  9. Import CSV file into SQL Server
  10. How to create a table from select query result in SQL Server 2008 [duplicate]
  11. Are there any disadvantages to always using nvarchar(MAX)?
  12. Creating an index on a table variable
  13. Is there a LastIndexOf in SQL Server?
  14. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  15. SQL Server – stop or break execution of a SQL script
  16. What is the difference between using a cross join and putting a comma between the two tables?
  17. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  18. How do I calculate a running total in SQL without using a cursor?
  19. Equivalent of Oracle’s RowID in SQL Server
  20. Concat field value to string in SQL Server
  21. SQL “between” not inclusive
  22. Benefits Of Using SQL Ordinal Position Notation?
  23. SQL Server replace, remove all after certain character
  24. Does UNION ALL guarantee the order of the result set [duplicate]
  25. Inserting rows into a table with one IDENTITY column only [duplicate]
  26. How to find sum of multiple columns in a table in SQL Server 2005?
  27. How to retrieve field names from temporary table (SQL Server 2008)
  28. When using GETDATE() in many places, is it better to use a variable?
  29. What does ON [PRIMARY] mean?
  30. Getting warning: Null value is eliminated by an aggregate or other SET operation

Leave a Comment