CODEWITHSUNDEEP
sqlsql-servernull
Igal Drayerman
Igal Drayerman

Reputation: 95

Using BIT datatype to filter a query in the WHERE clause

I'm building a stored procedure that uses a bit parameter. this bit parameter is used to filter the email column.

if @param is 1 - show all clients that has an email.

if @param is 0 - show all clients without an email.

and if @param is null (not passed to the procedure) - show both clients with and without an email.

that not going so well. any any suggestion?

where case @email when 1 then email is not null
                 when 0 then email is null)

Upvotes: 3

Views: 3217

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Use simple OR:

WHERE (@param IS NULL)
   OR (@param = 1 AND email IS NOT NULL)
   OR (@param = 0 AND email IS NULL)

Upvotes: 5

Related Questions