CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
Dominic
Dominic

Reputation: 175

Conditional WHERE in SQL clause

I need to put a WHERE condition, depending of the value of a parameter.

Here is the WHERE clause:

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'

Variable @Group is a parameter. If it equals to "ALL", I need to ignore the first part of the WHERE. How can I do this?

Upvotes: 1

Views: 91

Answers (3)

Kevin
Kevin

Reputation: 7309

It seems like this would be better handled with a 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'

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can add OR:

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

Upvotes: 2

Mureinik
Mureinik

Reputation: 311073

You can achieve this functionality with the logical or operator:

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')

Upvotes: 1

Related Questions