CASE statement within WHERE statement

Greetings, I would like to include CASE Statement inside my where statement as follows:

SELECT a1.ROWGUID FROM Table1 a1
INNER JOIN Table2 a2 on a1.ROWGUID=a2.Table1ROWGUID
WHERE a1.Title='title' 
AND (CASE WHEN @variable is not null THEN a1.ROWGUID in (SELECT * FROM #TempTable))

However, this 'CASE' statement does n开发者_Python百科ot work inside 'WHERE' statement. How can I do it correct?

You are missing END and possibly also an ELSE:

(CASE WHEN @variable is not null THEN a1.ROWGUID ELSE ... END)

You need to do something like this:

WHERE   a1.Title = 'title
AND     (@variable IS NULL
OR      a1.ROWGUID IN (SELECT * FROM #TempTable))

I'm guessing slightly because your case statement is incomplete.

A CASE statement is used to return a variable, not evaluate a predicate.

Therefore you could do

WHERE CASE WHEN @test1 = 'valuea' THEN 1 ELSE 2 END = MyTestColumn

to compare either 1 or 2 with your column based on some variable (or other column), but not in the way you have tried.

David M's answer is the correct way to do what you need to do.

Another alternative.

SELECT a1.ROWGUID FROM Table1 a1
INNER JOIN Table2 a2 on a1.ROWGUID=a2.Table1ROWGUID
INNER JOIN #TempTable ON  (@variable is null) OR #TempTable.ROWGUID = a1.ROWGUID
WHERE a1.Title='title' 

If this is for a significant number of rows you might want to be careful about plan caching issues with all these alternatives and just divide it into 2 alternative statements though.