SQL Server 2000存储过程分支参数

I want to create a stored procedure. If the parameter is -1 then there should not be a where clause on that column else there should be a WHERE clause. What's the best way to do it without a lot of IF branching?

我想创建一个存储过程。如果参数是-1，那么列上不应该有where子句，否则应该有where子句。如果没有分支，最好的方法是什么?

I checked the archive. There are a few similar questions but not exactly the same.

我检查了档案。有几个相似的问题，但并不完全相同。

CREATE PROCEDURE report
(
  @site int,
  @promo int,
  @type int
)
AS
SET NOCOUNT ON

-- I want to avoid this:
IF @site = -1 AND @promo = -1 and @type = -1
BEGIN
  SELECT * from table
END
IF @site > -1 AND @promo = -1 and @type = -1
BEGIN
  SELECT * from table WHERE site = @site;
END
... -- other cases


ELSE  -- all parameters are > -1
BEGIN
  SELECT * from table 
  WHERE site = @site AND promo = @promo AND type = @type
END

3 个解决方案

#1

This works in many cases, (despite what the comments will say without trying it) because the optimiser will ignore the ISNULL bit. Only works for non-null columns

这在很多情况下都是有效的(不管评论会怎么说，不要尝试)，因为optimiser会忽略ISNULL位。只适用于非空列

SELECT @site = NULLIF(@site, -1) ...

SELECT * from table  
  WHERE site = ISNULL(@site, site) ..

Otherwise, conditional WHERE which is usually bad because OR can not be optimised

否则，条件句通常是坏的，因为或不能优化

SELECT * from table  
  WHERE (@site = -1 OR site = @site) AND  (...

Or separate stored procedures (don't think you want that either)

或者单独的存储过程(您也不希望这样)

Or use sp_executesql (avoids dynamic SQL)

或者使用sp_executesql(避免动态SQL)

#2

How about:

如何:

SELECT * FROM table WHERE
  ((site = @site) OR (@site = -1)) AND
  ((promo = @promo) OR (@promo = -1)) AND
  ((type = @type) OR (@type = -1))

One caveat, though, you may find that SQL is not very intelligent in optimizing this sort of query.

不过，需要注意的是，您可能会发现，在优化此类查询时，SQL并不是非常智能。

#3

why fight against the obvious, simplest solution?

为什么要反对显而易见的、最简单的解决方案呢?

seriously, the branching solution make the intent clear, and can easily be understood by others.

认真地说，分支解决方案明确了意图，并且很容易被其他人理解。

#1