SQL子句中的条件WHERE

Question

我需要根据参数的值设置一个WHERE条件。

这是WHERE子句：

WHERE 
T1.SalesPerson IN (
SELECT VendorName FROM dbo.TB_grpVendor WHERE GroupName = @Group
) AND 
(
T0.DocDate >= @DateBegin AND T0.DocDate <= @DateEnd OR 
T0.DocDate >= @DateBegin2 AND T0.DocDate <= @DateEnd2
) AND 
T0.CANCELED <> 'Y'

变量@Group是一个参数。如果它等于"ALL"，我需要忽略WHERE的第一部分。我该怎么做？

Answer 1

您可以添加OR:

where (@Group = 'ALL' OR
EXISTS (SELECT 1 
FROM dbo.TB_grpVendor 
WHERE VendorName = T1.SalesPerson AND GroupName = @Group
)
) AND
. . . 

Answer 2

您可以使用逻辑or运算符实现此功能：

WHERE (@Group = 'ALL' OR 
T1.SalesPerson IN (SELECT VendorName FROM dbo.TB_grpVendor WHERE GroupName = @Group)) AND 
(T0.DocDate >= @DateBegin AND T0.DocDate <= @DateEnd OR  
T0.DocDate >= @DateBegin2 AND T0.DocDate <= @DateEnd2 ) AND 
(T0.CANCELED <> 'Y')

Answer 3

似乎用JOIN 处理会更好

INNER JOIN dbo.TB_grpVendor GV on T1.SalesPerson = GV.VendorName
AND @GroupName IN ('All', GV.GroupName)
WHERE
(
T0.DocDate >= @DateBegin AND T0.DocDate <= @DateEnd OR 
T0.DocDate >= @DateBegin2 AND T0.DocDate <= @DateEnd2
)
AND T0.CANCELED <> 'Y'