使用可选的存储过程参数

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子句,如:

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

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

但是,在某些情况下,WHERE条件更复杂:

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.

当我取消注释这些复杂的WHERE子句时,查询执行时间加倍,执行计划变得非常复杂。虽然执行计划不会打扰我,但将查询的执行时间加倍是一个明确的问题。

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?

这是动态SQL是更好的解决方案吗?

6 个解决方案

#1

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.

主要问题可能是参数嗅探,并且根据您的哪个参数为NULL,完全不同的最佳执行计划。尝试使用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.

与某些观点相反,Sql Server确实进行短路评估 - 尽管(与所有查询优化一样)它可能不是您想要的。

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

顺便说一句 - 我可能会将查询的那部分重写为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

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

它可能会也可能不会影响查询计划,但它对我来说有点清洁。

#2

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.

这将创建更简单的SQL,优化器将做得更好。

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.

你需要很多参数来重新设计,你需要动态的sql解决方案,然后也总是使用参数,你可能会被SQL-Injection错误所困扰。

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

组合也是可能的。您最有可能使用的查询/查询,并进行预编译。所有其他组合都是动态创建的。

#3

CASE statements are your friend...

CASE声明是你的朋友......

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

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...

我更倾向于使用CASE语句,因为我的可选参数可能使用某些值而不是NULL ...

#4

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

在这种情况下,动态SQL可能是更好的解决方案,特别是如果存储过程仅包装此一个查询。

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.

要记住的一件事是SQL Server不会在单个查询中短路布尔表达式。在许多语言中,如果a为真,则“(a)||(b)”不会导致b被评估。同样,如果a为假,则“(a)&&(b)”不会导致b被评估。在SQL Server中,情况并非如此。因此,在您给出的示例中,即使@NewGroupId不为null,也将评估“或”后端的查询。

#5

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

对于少量可选参数,从GvS建议的几个静态查询中选择一个条件是可以的。

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!

但是,如果有多个参数,这会变得难以处理,因为您需要处理所有排列 - 使用5个参数即32个静态查询!使用动态SQL,您可以构造最适合给定参数的精确查询。一定要使用绑定变量!

#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

#1