Tim Mackey's Weblog

I found this out today, if you want a stored procedure to handle optional values without limiting the result set when the parameter is NULL, you can use a CASE/WHEN statement to ignore the parameter in the where clause if it is null.  you just set ColumnX=ColumnX via the CASE statement, which will always be true, effectively resulting in (TRUE AND {remaining condition})

Procedure dbo.Select_Totals_For_Year
(
   @Name NvarChar(200),
   @Year int
)
AS
BEGIN
select Name, Count(ID) AS [Total]
FROM Table1
WHERE  Name = @Name AND
                 Year = (CASE WHEN @Year is null THEN Year ELSE @Year END)
group by Name
order by Name
END