SQL Server Performance

if statement in where clause

Discussion in 'T-SQL Performance Tuning for Developers' started by peterswan, Dec 11, 2003.

  1. peterswan New Member

    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 &gt; @StartDate<br />AND Pr.Submitted_Date_Time_dt &lt; @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 />
  2. gaurav_bindlish New Member

    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
  3. bambola New Member

    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') )
  4. Foralarx New Member

    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'



  5. peterswan New Member

    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

Share This Page