Most of us have used conditional where clause in our queries to get desired output for multiple parameter
WHERE (@Variable1 IS NULL OR Column1 = @Variable1)
AND (@Variable2 IS NULL OR Column2 = @Variable2)
AND (@Variable3 IS NULL OR Column3 = @Variable3)
... and so on
This is a horrible way is the no of columns/parameters is large (from what I have observed more than 3 or 4 makes it horribly slow). and a solution to it is recompile
Another way would be the use of dynamic query
Set @query = 'Select column1, column2, column3 from table where '
Set @where = @Where + Case when @variable1 IS NOT NULL THEN 'AND Column1 = @Variable1' Else '' END
Set @where = @Where + Case when @variable2 IS NOT NULL THEN 'AND Column2 = @Variable2' Else '' END
... and so on
I am too lazy to type the entire codes for dynamic query :P
My questions are: in both the cases
- Conditional where with recompile
- Dynamic query execution with
sql_execute
The query plan will be generated again for each execution, So which one of these methods should be preferred for the ease of maintenance or any other factor. or do both of them provide their uses in different situations and I have completely misunderstood them?
