带参数分支的 SQL Server 2000 存储过程 [英] SQL Server 2000 stored procedure branching with parameters

问题描述

我想创建一个存储过程.如果参数为 -1，则该列上不应有 where 子句，否则应该有 WHERE 子句.没有大量 IF 分支的最佳方法是什么?

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?

我检查了存档.有几个类似的问题，但不完全相同.

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

推荐答案

这在很多情况下都有效，(尽管没有尝试它的评论会说)因为优化器将忽略 ISNULL 位.仅适用于非空列

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

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

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

否则，条件 WHERE 通常很糟糕，因为 OR 无法优化

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)

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

Or use sp_executesql (avoids dynamic SQL)

这篇关于带参数分支的 SQL Server 2000 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！