rajawat.niranjan (8/9/2012)
@dwain.cI can Use IF/Else but there i'll have to use too many iterations,
Because @Country and @province may contain more that one value.
and each table City, Region, Country and Province contains a translation and also a synonym table.
It will make too many iterations.
@SpringTownDBA
In this solution how can i pass multiple values in parameter
declare @p1 varchar(100) = 'Evere,anvers'
select top 10 *
from TB_City ci
where (@p1 is null or @p1 =ci.CityDefaultName)
option (recompile)
First of all, I think you should have posted a better subject to the question as it seems more likely the case of dynamic search 😉
I agree that IF/ELSE is not the way to go. It would make the code too long and would make it very hard for the optimizer to come up with a good plan unless the options like procedure recompile etc are used.
For multiple values in parameter, as mentioned by SpringTownDBA, TVP is an option. Another option could be to use Jeff Moden's 8k splitter function[/url]
But if you have opted for the dynamic sql, then it seems fine to me. (Although I do not like dynamic sql) I have seen proper implementation can get you a very optimized solution for dynamic searches. I would strongly recommend reading this great article by Erland Sommarskog on dynamic search.