Working with optional stored procedure parameters

Question

I am working on a stored procedure with several optional parameters. Some of these parameters are single values and it's easy enough to use a WHERE clause like:

WHERE (@parameter IS NULL OR column = @parameter)

However, in some instances, the WHERE condition is more complicated:

WHERE (@NewGroupId IS NULL OR si.SiteId IN (SELECT gs.SiteId
FROM [UtilityWeb].[dbo].[GroupSites] AS gs
WHERE gs.GroupId = @NewGroupId))

When I uncomment these complicated WHERE clauses, the query execution time doubles and the execution plan becomes remarkably more complicated. While the execution plan doesn't bother me, doubling the execution time of a query is a definite problem.

Is there a best practice or pattern that others have found for working with optional parameters in their stored procedures?

Is this one of those instances where dynamic SQL would be a better solution?

Answer 1

I would create separate queries for the parameter being available or not.

This will create simpler SQL, and the optimizer will do a better job.

Like this:

if (@parameter IS NULL) then begin
   select * from foo
end
else begin
   select * from foo where value = @parameter
end

In you have to many parameters to redesign like this, and you go for the dynamic sql solution, then also always use parameters, you might get bitten by the SQL-Injection bug.

A combination is also possible. The most likely used query/queries you code in full, and get precompiled. All other combinations are created dynamically.

Answer 2

The main problem is likely to be parameter sniffing, and wildly different optimal execution plans depending on which of your parameters are NULL. Try running the stored proc with RECOMPILE.

Contrary to some beliefs, Sql Server does do short circuit evaluations - though (as with all query optimizations) it may not be exactly what you wanted.

BTW - I would probably rewrite that portion of the query as a JOINed derived table:

SELECT * 
FROM Table as si
JOIN (
  SELECT SiteId
  FROM [UtilityWeb].[dbo].[GroupSites]
  WHERE GroupId = ISNULL(@NewGroupId, GroupId)
  /* --Or, if all SiteIds aren't in GroupSites, or GroupSites is unusually large 
     --this might work better
  SELECT @newGroupId
  UNION ALL
  SELECT SiteId FROM [UtilityWeb].[dbo].[GroupSites]
  WHERE GroupId = @NewGroupId
  */
) as gs ON
  si.SiteId = gs.SiteId

It may or may not influence the query plan, but it's a bit cleaner to me.

Answer 3

CASE statements are your friend...

Rather than:

if (@parameter IS NULL) then begin
   select * from foo
end
else begin
   select * from foo where value = @parameter
end

You can use:

SELECT * FROM foo 
WHERE value = CASE WHEN @parameter IS NULL THEN value ELSE @parameter END

Or

SELECT * FROM foo 
WHERE value = ISNULL(@parameter,value)

I tend to use CASE statements more because my optional parameters may use certain values instead of NULL's...

Answer 4

Dynamic SQL is probably a better solution in this case, particularly if the stored procedure only wraps this one query.

One thing to keep in mind is that SQL Server doesn't do short circuiting of boolean expressions inside a single query. In many languages "(a) || (b)" will not cause b to be evaluated if a is true. Similarly, "(a) && (b)" will not cause b to be evaluated if a is false. In SQL Server, this is not the case. So in the example you give, the query on the back end of the "or" will get evaluated even if @NewGroupId is not null.

Answer 5

For a small number of optional parameters, conditional choosing from one of several static queries as GvS suggests is OK.

However, this becomes unwieldy if there a several parameters, since you need to handle all permutations - with 5 parameters that is 32 static queries! Using dynamic SQL you can construct the exact query that best fits the parameters given. Be sure to use bind variables though!

Answer 6

IMHO, the parameter sniffing issue can be solved by copying all parameters into variables; then avoid using the parameters directly at all cost, use the variables instead. Example:

create proc ManyParams
(
    @pcol1 int,
    @pcol2 int,
    @pcol3 int
)
as
declare
    @col1 int,
    @col2 int,
    @col3 int

select
    @col1 = @pcol1,
    @col2 = @pcol2,
    @col3 = @pcol3

select 
    col1,
    col2,
    col3
from 
    tbl 
where 
    1 = case when @col1 is null then 1 else case when col1 = @col1 then 1 else 0 end end
and 1 = case when @col2 is null then 1 else case when col2 = @col2 then 1 else 0 end end
and 1 = case when @col3 is null then 1 else case when col3 = @col3 then 1 else 0 end end