March 26, 2011 at 6:19 pm
Hi,
I have a stored proc to which I will be passing a parameter, which is of type Varchar(100). This parameter will be used in the where clause to filter the select statement. While some times a value will be passed, in other times a null will be passed. Whenever null was passed, the query should return all the records - essentially ignoring the filter, and whenever a value is passed, it should send only those records filtered by the parameter.
Create Procedure
{
@LastName Varchar(100)
}
as
select * from TBS_Names where lastname = @LastName
go
The above sp is a simplified one, and the actual query is pretty complex. I don't want to have a big if statement and two separate select statements, one when the parameter is null and another when it is not null. I believe it could be done elegantly within a single select statement using case-when statement. I tried for a while but couldn't achieve the desired result. Any help is greatly appreciated.
Thanks
March 26, 2011 at 8:27 pm
Create Procedure
{
@LastName Varchar(100) = null
}
as
select * from TBS_Names where (@LastName is null or lastname = @LastName)
go
But, before you go with this approach, also please read this blog about performance issues that may result from plan caching: http://blogs.msdn.com/b/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx
March 26, 2011 at 11:48 pm
Hi SunitaBeck,
Thanks for the help. The reference you have mentioned was extremely good!
Thanks
March 27, 2011 at 3:24 am
Read through this as well: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/. sunitabeck's solution does not perform well on larger resultsets.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply