select top 20 id
from Employee e
where e.state=IsNull(@State,e.state)
and e.industry=IsNull(@Industry,e.Industry)
Two issue I see here is use if ISNULL() function in WHERE clause and using parameter in WHERE condition which makes it difficult to determine whether to use index seek or not.
Per my observation, you can either change your WHERE condition to be like
where e.state is null or e.state = @State
and e.industry is null or e.Industry = @Industry
(OR) try using a dynamic query instead like
declare @sql varchar(200);
declare @cond varchar(100);
set @sql = 'select top 20 id from Employee e ';
if(@State is not null)
set @cond = @cond + ' and e.state = @State'
if(@Industry is not null)
set @cond = @cond + ' and e.industry = @Industry'
IF len(@cond) > 0
SET @sql = @sql + ' WHERE ' + RIGHT(@cond, LEN(@cond)-3)
Dynamic query idea taken from Here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments