Database Reference
In-Depth Information
Even though a statement-level recompile solves the problem, it introduces the overhead of constant recompilations,
which you would like to avoid, of course, if stored procedures are called very often. One of the options that you have is to
write multiple queries using IF statements covering all possible combinations of parameters. SQL Server would cache
the plan for each statement in that case.
Listing 26-13 shows such an approach, however it quickly becomes unmanageable with a large number of
parameters. The number of combinations to cover is equal to the number of parameters squared.
Listing 26-13. Plan Reuse: Covering all possible parameter combinations
alter proc dbo.SearchEmployee
(
@Number varchar(32) = null
,@Name varchar(100) = null
)
as
begin
if @Number is null and @Name is null
select Id, Number, Name, Salary, Country
from dbo.Employees
else if @Number is not null and @Name is null
select Id, Number, Name, Salary, Country
from dbo.Employees
where Number=@Number
else if @Number is null and @Name is not null
select Id, Number, Name, Salary, Country
from dbo.Employees
where Name=@Name
else
select Id, Number, Name, Salary, Country
from dbo.Employees
where Number=@Number and Name=@Name
end
In the case of a large number of parameters, dynamic SQL becomes the only option. SQL Server will cache the
execution plans for each dynamically generated SQL statement. Listing 26-14 shows such an approach. Remember
that using dynamic SQL breaks ownership chaining, and it always executes in the security context of CALLER.
Listing 26-14. Plan Reuse: Using dynamic SQL
alter proc dbo.SearchEmployee
(
@Number varchar(32) = null
,@Name varchar(100) = null
)
as
begin
declare
@SQL nvarchar(max) = N'
select Id, Number, Name, Salary, Country
from dbo.Employees
 
Search WWH ::




Custom Search