.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

Tuesday, 24 April 2007 00:31:00 (GMT Daylight Time, UTC+01:00)
Very nice.

Now if there was only a way to allow null values and operate on some other flag value for the column.
Glenn
Wednesday, 30 May 2007 07:23:01 (GMT Daylight Time, UTC+01:00)
It works brilliantly, thanks
Mduduzi
Tuesday, 30 June 2009 21:55:50 (GMT Daylight Time, UTC+01:00)
Tim, I've been pulling my hair out for the last 2 hours trying to figure something like this out. Thanks!
Greg
Wednesday, 03 March 2010 14:19:29 (GMT Standard Time, UTC+00:00)
Good work, Thanks
Sampath
Tuesday, 04 May 2010 12:33:34 (GMT Daylight Time, UTC+01:00)
that's great, thanks.
Wednesday, 11 April 2012 15:47:58 (GMT Daylight Time, UTC+01:00)
You made it look easy. Thanks!
chuckb35
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview