CODEWITHSUNDEEP
sqlsql-servercasewhere-clausesql-null
jw11432
jw11432

Reputation: 593

Alter WHERE clause based on parameter

What I'm trying to do is based upon a parameter value, alter the WHERE clause. I've tried using a CASE expression, but realizing I'm trying to use it for a purpose it was not intended for. Here's my attempt:

WHERE CASE WHEN @active = 1 THEN vp.date_installed IS NOT NULL AND vp.date_removed IS NULL 
         WHEN @active = 0 THEN vp.date_removed IS NOT NULL 
         END

Of course the issue here is trying to use the THEN to perform beyond a simple value retrieval. So how would I go about this?

Upvotes: 1

Views: 78

Answers (1)

GMB
GMB

Reputation: 222682

I would recommend boolean logic instead:

WHERE 
    (@active = 1 AND vp.date_installed IS NOT NULL AND vp.date_removed IS NULL)
    OR (@active = 0 AND vp.date_removed IS NOT NULL)

Upvotes: 3

Related Questions