T.Ashish (7/29/2013)
Parameters goes like this:(isnull(@activity_id,0)=0
or
pd.activity_id = @activity_id)
and
(isnull(@threshold,'ALL')='ALL'
or
pd.threshold_level = @threshold)
As I said in my previous post, you should use OPTION(RECOMPILE) for this type of query. If you have indexes on activity_id and threshold_level, they may be very good for your query, if the parameter is for a specific activity or threshold - but uselss if the parameter is 0 or ALL.
OPTION(RECOMPILE) may not do everything for you, but you should start there.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]