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.

Answers


...
WHERE @OnlyIncomplete = 0
    OR (@OnlyIncomplete = 1 AND Completed_Date IS NULL)

Need Your Help

Can't use header-redirect from PHP to Javascript

javascript php html redirect header

I'm trying to make my PHP file redirect to an HTML file.

Create buttons from a text array

objective-c cocoa-touch ios uiwebview uibutton

Sorry I can't think of a good title for this but here' my situation:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.