Using a bit input in stored procedure to determine how to filter results in the where clause
I'm beating my head against the wall here... can't figure out a way to pull this off.
Here's my setup:
My table has a column for the date something was completed. If it was never completed, the field is null. Simple enough.
On the front end, I have a checkbox that defaults to "Only show incomplete entries". When only pulling incomplete entries, it's easy.
SELECT * FROM Sometable WHERE Completed_Date IS NULL
But offering the checkbox option complicates things a great deal. My checkbox inputs a bit value: 1=only show incomplete, 0=show all.
The problem is, I can't use a CASE statement within the where clause, because an actual value uses "=" to compare, and checking null uses "IS". For example:
SELECT * FROM Sometable WHERE Completed_Date IS <---- invalid syntax CASE WHEN ... END SELECT * FROM Sometable WHERE Completed_Date = CASE WHEN @OnlyIncomplete = 1 THEN NULL <----- this translates to "WHERE Completed_Date = NULL", which won't work.. I have to use "IS NULL" ... END
Any idea how to accomplish this seemly easy task? I'm stumped... thanks.
... WHERE @OnlyIncomplete = 0 OR (@OnlyIncomplete = 1 AND Completed_Date IS NULL)