.Net ramblings
# Tuesday, 17 April 2007
SQL: optional parameters in stored procedure where clause
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

Tuesday, 17 April 2007 14:43:30 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database