Complex WHERE expression using CASE-THEN in SQL 2008

Question

I have a query:

SELECT ID FROM requests WHERE date <operator> <expression>

I need to change <expression> relying on my stored procedure's parameter.

If flag is set: WHERE date BETWEEN GETDATE() - 7 AND GETDATE()

If is not: WHERE date = date

I tried to use CASE-THEN but it forces using concrete operator but I have to have few:

SELECT ID FROM requests WHERE date = -- here it is
CASE
  WHEN @flag = 1 THEN ..
  ELSE date
END

Answer 1

select 
    id
from
    requests
where
    (
        flag = 1
        and
        date between getdate()-7 and getdate()
    )
    or
    (
        flag <> 1
        and
        date = date
    )

Answer 2

SELECT ID FROM requests WHERE 
    (date = date and @flag <> 1 ) 
    or ( date BETWEEN GETDATE() - 7 AND GETDATE() and @flag = 1)

Or in the Transact-SQL

if @flag = 1
    then
     SELECT ID FROM requests WHERE date = date
    else
     SELECT ID FROM requests WHERE date BETWEEN GETDATE() - 7 AND GETDATE()

Answer 3

You can simplify it using the good old IF statement

if @flag = 1
   SELECT ID FROM requests WHERE date BETWEEN GETDATE() - 7 AND GETDATE()
else
   SELECT ID FROM requests

EDIT: I am not sure if it works. But, here is what I added in the comment

SELECT ID FROM requests 
WHERE 
CASE WHEN @flag = 1 THEN date BETWEEN GETDATE() - 7 AND GETDATE() 
ELSE 1 = 1 
END

EDIT2: I think it makes sense to use IF statement (if it is in TSQL). Because, the condition relies on external variable other than something from the row

i.e. CASE will do a comparison of @flag for each of the row, which is not needed.

Answer 4

As you found out, you can't use a CASE statement in the WHERE clause. Your alternatives are:

Option 1: WHERE clause

SELECT t.id 
  FROM REQUESTS t
 WHERE 1 = 1
    OR (@flag = 1 AND t.date BETWEEN GETDATE() - 7 AND GETDATE())

Option 2: Use IF and have two (or more depending on situation) distinct SQL statements

IF @flag = 1
   SELECT t.id 
     FROM REQUESTS t
ELSE
   SELECT t.id 
     FROM REQUESTS t
    WHERE t.date BETWEEN GETDATE() - 7 AND GETDATE())

Option 3: dynamic SQL (Everyone's favorite)

DECLARE @SQL nvarchar (4000)

SET @SQL = 'SELECT t.id 
              FROM REQUESTS t 
             WHERE 1 = 1'

IF @flag = 1 
  SET @SQL = @SQL + ' AND t.date BETWEEN GETDATE()-7 AND GETDATE()'

EXEC sp_executesql @SQL

Personally, I loath to deal with Option 2. Near identical statements make me want to refactor into a single SQL query. That said, use #1 if things aren't too complex. Otherwise, use dynamic.