November 2, 2007 at 4:07 am
The following statement doesn't use the index on lngbcid. When i have a hardcoded lngbcid in the ELSE, the index is used. Why?
What i like to do:
A function in the datalayer has 4 parameters for a search. One or more params could be NULL. How can I write the where-Clause that the DB searches only in the attributes which are not NULL?
Thanks
declare @int int
set @int = 1
select * from dbo.tabBC
where lngbcid =
(CASE @int
WHEN 1 THEN @int
else lngbcid
END)
November 2, 2007 at 5:25 am
The optimiser can't properly see the results of the case statement at compile time and probably chooses worst possible case (column = column) where no index usage is possible.
The way you're doing the query will work, but that kind of all-in-one search query generally does not have an optimal execution plan.
If the number of fields is small, it may be possible to construct multiple stored procs, depending which parameters are passed (as an example , 1 proc called if the surname is passed, one if the surname is not, but the ID number is, one if neither surname or id number is passed but email address is)
Dynamic SQL is also a potential solution, if the number of parameters is high, but beware of the downsides of dynamic sql (http://www.sommarskog.se/dynamic_sql.html)
Hope that helps.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply