Work with additional stored procedure parameters

Question

I am working on a stored procedure with several optional parameters. Some of these parameters are single values, and simply use the WHERE clause, for example:

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

However, in some cases, the WHERE clause is more complex:

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 complex WHERE clauses, the query execution time doubles and the execution plan becomes much more complicated. Although the execution plan does not bother me, doubling the execution time of the request is a definite problem.

Is there a best practice or template that others have found to work with optional parameters in their stored procedures?

Is this one of those cases where dynamic SQL will be the best solution?

Answer 1

, , sniffing , NULL. RECOMPILE.

, Sql Server do - ( ) , .

BTW - , , JOINed:

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

, .

Answer 2

.

SQL, .

:

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

, sql, , SQL-Injection .

. /, , . .

Answer 3

CASE - ...

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

:

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

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

CASE, NULL...

Answer 4

SQL, , , .

, SQL Server . "(a) || (b)" b , a . , "(a) & (b)" b , a . SQL Server . , , , "" , @NewGroupId .

Answer 5

, GvS, .

However, this becomes cumbersome if there are several parameters, since you need to handle all permutations - with 5 parameters, which are 32 static queries! Using dynamic SQL, you can build the exact query that best matches the specified parameters. Be sure to use bind variables though!

Answer 6

IMHO, the problem of smelling a parameter can be solved by copying all the parameters into variables; then avoid using parameters directly at all costs, use 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