SQL Server: "All Values" parameters in the WHERE clause

Recently, I put a parameter in a WHERE clause in a stored procedure so my client could select values from a list in SQL Reporting Services:

projectID	projectTypeID	projectManagerID	projectName
1	100	200	TPS Reports
2	100	201	Interest rounding exploit
3	101	200	FLARE evaluation
4	101	201	B.E. Channel Referral

select *
from projects
where projectTypeID = @projectTypeID

The client liked it a lot, but then he wanted to add an "All Project Types" option to the list. When the "All Project Types" option was selected, the parameter would be set to -1, and the WHERE clause would essentially go away.

I initially solved this with a temporary table and an IF clause, but found that SQL Reporting Services is squirrely about temporary tables. So I found this sweet little solution:

select * from projects
where @projectTypeID = -1
or projectTypeID = @projectTypeID

Which is really quite smart, isn't it? When the parameter is -1, the first part of the WHERE clause is TRUE, and TRUE OR anything is TRUE. When the WHERE clause evalutuates to TRUE always, then all rows are returned. If @projectTypeID is not -1, for example 100, then the first part of the WHERE clause is FALSE, but FALSE ORed with TRUE is still TRUE. So in our example of @projectTypeID = 100, two rows will be returned.

There is one last wiggle. My client wanted to be able to specify "All Project Types" and "All Project Managers". The same logic works, but you have to write the WHERE clause correctly:

select * from projects
where (@projectTypeID = -1 or projectTypeID = @projectTypeID)
and (@projectManagerID = -1 or projectManagerID = @projectManagerID)


See if you can trick out the logic your self on this.

Special thanks to Ilo at Ilo's Quest for this trick.