SQL Server Performance Forum – Threads Archive
if statement in where clause
Hello,<br /><br />I’m trying add an if statement in a where clause but I’m getting an error:<br /><br />where<br />Pr.Submitted_Date_Time_dt > @StartDate<br />AND Pr.Submitted_Date_Time_dt < @EndDate<br />if @IDType = ‘DealerID'<br />begin<br />AND prf.Supplier_Type_id_int = 1<br />end<br /><br /><br />How can I do something like this without writing up a whole new select statement?<br /><br />Thanks, [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Peter <br />
Well I guess you’ll have to use a Case statement or dynamically generate query or write 2 queries for the same… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
where
Pr.Submitted_Date_Time_dt > @StartDate
AND Pr.Submitted_Date_Time_dt < @EndDate
AND ( (@IDType = ‘DealerID’ AND prf.Supplier_Type_id_int = 1) OR (@IDType <> ‘DealerID’) )
As g_b suggested you can use a case like this (but I suspect bambola’s is the quicker way to return results). WHERE Pr.Submitted_Date_Time_dt > @StartDate AND
Pr.Submitted_Date_Time_dt < @EndDate AND CASE @IDType
WHEN ‘DealerID’ THEN prf.Supplier_Type_id_int
ELSE 1 (or opposite field to ‘prf.Supplier_Type_id_int = 1’ to compare against)
END = CASE @IDType
WHEN ‘DealerID’ THEN 1
ELSE 1 (or opposite value to ‘prf.Supplier_Type_id_int = 1’ to compare against)
END So that gives you either:- ‘prf.Supplier_Type_id_int = 1’ where @IDType = ‘DealerID’
or ‘1 = 1’ where @IDType <> ‘DealerID’
Thanks to all who contributed to this thread.<br /><br />Most helpful! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Peter
]]>