如何在存储过程中动态添加到 TSQL Where 子句

问题描述

如何给TSQL Where子句动态添加条件?

我试图避免在两个完整的 Select 语句之间进行选择.下面的代码非常相似，但没有我的实际代码那么复杂.

How to dynamically add conditions to the TSQL Where clause?

I'm trying to avoid selecting between two complete Select statements. The following is very similar, but less complicated, than my actual code.

SELECT COUNT(*)
FROM   MyTable
WHERE  ShipDate >= @FirstDayOfMonth
AND    ShipDate <  @LastDayOfMonth
AND    OrderType = 1
AND    NOT OrderCode LIKE '%3'
AND    NOT OrderCode LIKE '%4';

我希望能够根据存储过程参数的值添加或删除最后两个条件.

即:如果 @ExcludePhoneOrders = False ，请不要使用最后两个 Where 子句条件.

I would like to be able to add or remove the last two conditions depending on the value of a stored procedure parameter.

i.e.: if @ExcludePhoneOrders = False , don't use the last two Where clause conditions.

推荐答案

我不确定你是想包含还是排除，但这里有一个小插曲.

I'm not sure if you meant to include or exclude, but here is a stab at it.

SELECT COUNT(*)
FROM   MyTable
WHERE  ShipDate >= @FirstDayOfMonth
AND    ShipDate <  @LastDayOfMonth
AND    OrderType = 1
AND    (@ExcludePhoneOrders = False OR (NOT OrderCode LIKE '%3' AND NOT OrderCode LIKE '%4'));