T-SQL：使用动态比较运算符（=，<=，>=，...）进行筛选

Question

我将创建一个报表工具，用户可以选择运算符和2个要筛选的值。

我的基本表格：

UserID     UserName
-------------------------------
1          User1
2          User2
3          User3
4          User4
5          User5

用户可以选择一个操作员，我希望将其翻译为这样：

Option      SQL Operator
------------------------------
between         column between x and y
like            column '%' + x + '%'
greater than    column > x
less than       column < x
equal to        column = x
not equal to    column <> x

我在考虑类似于以下内容的东西：

... column = ISNULL(@parameter, column)

这里讲的是如果你传入一个值或者不传值，查询都会正确执行。

我正在尝试使用以下TSQL（** 不可用 *）：

declare @bwValue1 varchar(200) = '2', --between value 1
@bwValue2 varchar(200) = '4'; --between value 2

select * from users where
(UserID BETWEEN @bwValue1 AND @bwValue2 
OR UserID != @bwValue1 
OR UserID = @bwValue1 
OR UserID < @bwValue1 
OR UserID > @bwValue1 
OR UserID LIKE '%' + @bwValue1 + '%');

有没有一种方法可以编写TSQL，无论选择哪个运算符都可以正确评估语句？

* 最终答案 *

以下是我最终得出的结果，供任何感兴趣的人参考：

declare @fn varchar(200) = 'carl',
    @Op varchar(3) = 'bw',
    @bwValue1 varchar(200) = '978',
    @bwValue2 varchar(200) = '2000'

select * from users where userfirstname like '%' + @fn + '%' 
       AND ((@Op = 'eq' AND (userid = @bwValue1))
 OR (@Op = 'neq' AND (userid <> @bwValue1))
 OR (@Op = 'lt' AND (userid < @bwValue1))
 OR (@Op = 'gt' AND (userid > @bwValue1))
 OR (@Op = 'li' AND (userid like '%' + @bwValue1 + '%'))
 OR (@Op = 'bw' AND (userid between @bwValue1 and @bwValue2)))

- Losbear

---

根据指定的参数构建动态SQL。 - Yuriy Galanter

是的，我试图避免这样做。需求说明要我使用“参数化SQL”，以便SQL可以缓存存储过程以备将来使用、执行计划等。 - Losbear

我认为，使用如此多种参数，SQL无法创建一个在所有情况下都能良好运行的正常稳定计划。 - Yuriy Galanter

你不能将操作符如 > 或 < 参数化。只能参数化值。使用动态 SQL。如果你打开了针对即席工作负载的优化，除了那些已经执行多次的查询变体外，你不需要为昂贵的计划缓存膨胀付费。 - Aaron Bertrand

Answer 1

动态SQL并不意味着您不能将其参数化并缓存计划。这样做很好，因为SQL Server无法区分。如果您的应用程序连接了几个SQL字符串（不包含动态文字！），则SQL Server将像处理任何其他查询一样进行缓存。它会缓存计划。当然，每个运算符将产生不同的计划。如果查询可以以这种方式在索引上查找，则可能正是您想要的！所以我建议您使查询静态，除了运算符之外。如果您无法这样做并且愿意放弃SARGability，请执行以下操作：

WHERE 0=0
 OR (Operator = '=' AND (A = B))
 OR (Operator = '<' AND (A < B))
 OR (Operator = '>' AND (A > B))
 ---...

在运行时，只有一个OR子句将成为“活动”状态。这仍然易读且易于维护。

- usr

---

BETWEEN 有点繁琐。但这与我在这里的回答类似。 - Aaron Bertrand

你救了我的一天...在使用OR之前最好设置一个AND条件 WHERE col1='aaa' AND ( (Operator = '=' AND (A = B)) OR (Operator = '<' AND (A < B)) OR (Operator = '>' AND (A > B)) - ashraf mohammed

Answer 2

我实现这个的一种廉价方法是使用布尔比较器来指定我想要的比较。

WHERE (A = B AND @equalOp = 1) OR (A > B AND @gtOp = 1) OR (A < B AND @ltOp = 1)

简单易懂，甚至不需要动态SQL。查询优化器应该使用布尔值来最小化查询，从而防止实际比较发生。我会再次检查一下这个问题，但对我来说效果非常不错！

- Codeman

---

谢谢，我刚才有点傻。:) 今天我在几种语言之间切换了一下。 - Codeman