SQL Server Date Check based on input

Question

This problem has bugged me so many times and i have now decided to try and find the proper solution for it, instead of my long-winded/dirty/horrible way I always revert to (copying the sql statement)

The table has a date column with a default value of NULL

Now what i need to do is pass a value (-1,0,1) to an sql statement which determines what to pull back

-1 - should bring back all rows
0  - should bring back rows with a NULL date value
1  - should bring back rows with a valid date value

I have tried using CASE statements, but the logical operator would need to change depending on the value being passed to the query.

Lets just call the table Quotes and the column completed, so...

CREATE TABLE 'Quotes'
(
    completed DATETIME default(NULL)
)

Solution needs to be for SQL Server 2000, and i'm working in stored procedures not dynamic SQL. So it really needs to be all in one statement.

---

Thanks Dan

Answer 1

Something like this in the WHERE clause

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

Answer 2

Try this:

declare @param int
set @param=-1
declare @sql varchar(2000)
set @sql='select * from quotes '+ 
case @param when 0 then 'where completed is null'
      when 1 then 'where completed is not null'
      when -1 then ''
end
exec(@sql)

Raj