适用于同一字段的 TSQL 可选参数

Question

>我有一个存储过程，其中我最多将产品类型 （prod_type） 作为参数传入四次。这些参数中的每一个都可能为空，在这种情况下，应忽略它们。他们应该在每种商品类型之间有一个 OR 运算符，以便我们收集所有要求的商品类型。

如果可能的话，我想避免使用通过sp_ExecuteSQL传递的动态 SQL（我知道通过这个很容易）。

下面讨论我正在尝试做的事情的简化、孤立版本：

CREATE TABLE #Products (Prod_ID int, prod_type int);
INSERT INTO #Products
SELECT 1, 2
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 3, 3
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 4, 5
DECLARE @prod_type1 as int;
DECLARE @prod_type2 as int;
DECLARE @prod_type3 as int;
DECLARE @prod_type4 as int;
SET @prod_type1 = NULL;
SET @prod_type2 = 2;
SET @prod_type3 = NULL;
SET @prod_type4 = 3;
Select * from #Products
WHERE ((prod_type = ISNULL(@prod_type1,prod_type)) 
OR (prod_type = ISNULL(@prod_type2,prod_type)) 
OR (prod_type = ISNULL(@prod_type3,prod_type) 
OR (prod_type = ISNULL(@prod_type4,prod_type))))

我想被执行的地方是这样的：

Select * from [Product].[Product]
WHERE (prod_type = 2 OR prod_type = 3)

显然，上述使用 ISNULL 的解决方法将不起作用，因为 ISNULL 方法会将每行的产品类型与自身进行比较，这将导致积极的"命中"。COALESCE将面临同样的问题。

任何人都可以提出不涉及使用sp_ExecuteSQL的解决方案吗？

Answer 1

试试这个：

DECLARE @ProdTypes TABLE (Prod_Type INT NOT NULL);
INSERT INTO @ProdTypes (Prod_Type) VALUES (2);
INSERT INTO @ProdTypes (Prod_Type) VALUES (3);
SELECT pr.*
FROM #Products pr
WHERE EXISTS (SELECT 1
              FROM @ProdTypes pt
              WHERE pt.Prod_Type = pr.Prod_Type);

使用EXISTS允许逻辑上OR条件。如果找到匹配项，EXISTS将退出，因此它不会遍历@ProdTypes中的所有行，除非：

• 没有匹配的值
• 匹配值是集合中的最后一个 ;-）

但很多时候它会在扫描整个表变量之前退出。

编辑：
此外，不应将多个@Prod_TypeX参数传递给过程。相反，传入 CSV 整数列表，并使用基于 SQLCLR 或 XML 的字符串拆分器将其插入到表变量中，如下所示：

INSERT INTO @ProdTypes (Prod_Type)
   SELECT CONVERT(INT, csv.SplitVal)
   FROM   dbo.SQLCLRorXMLbasedSplitter(@ProdTypesCSV) csv;

Answer 2

试着像这样改变你Where Clause。

Select * 
from 
#Products
WHERE prod_type in(@prod_type1,@prod_type2,@prod_type2,@prod_type4)