title: Using a CASE or IF..ELSE within WHERE clause's AND part - SQL Server
body: <p>I have a stored procedure in SQL Server. 
  The SELECT part for this SP is as follows :</p>
  
  <pre><code>SELECT * FROM demoTable dt
  INNER JOIN demoTable2 dt2
  WHERE
        (@StartDate IS NULL OR dt.StartDate = @StartDate OR (dt.StartDate &gt;= @StartDate and dt.EndDate &lt;= @EndDate))   
        AND (@EndDate IS NULL OR dt.EndDate = @EndDate OR (dt.StartDate &gt;= @StartDate and dt.EndDate &lt;= @EndDate))    
  </code></pre>
  
  <p>Now I want to modify the above WHERE clause part after the AND condition.
  I have added 3 new parameters/variables which are being passed to this SP.</p>
  
  <pre><code>@ID int , @isAdmin int and @Date varchar(50)
  </code></pre>
  
  <p>Using the values of these variables I'm trying to manipulate the above WHERE clause as follows :</p>
  
  <pre><code> WHERE
     @StartDate IS NULL OR dt.StartDate = @StartDate OR (dt.StartDate &gt;= @StartDate and dt.EndDate &lt;= @EndDate))   
      -- Logic change below          
      AND(
      CASE WHEN (@ID != 100 AND @isAdmin != 1)
      THEN (@EndDate IS NULL OR dt.EndDate = @EndDate OR (dt.StartDate &gt;= @StartDate and dt.EndDate &lt;= @EndDate))    
      ELSE (dt.EndDate&gt;sysdatetime()) 
      END)   
  </code></pre>
  
  <p>But after this modification I'm receiving an error.</p>
  
  <p>Error statement : </p>
  
  <pre><code>Incorrect syntax near the keyword 'IS'.
  </code></pre>
  
  <p>This error is pointing to the line where I've used IS NULL from the modified logic part.</p>
  
  <p>Basically what I want to do is to apply the original logic for <strong>AND</strong>  i.e. </p>
  
  <pre><code>(@EndDate IS NULL OR dt.EndDate = @EndDate OR (dt.StartDate &gt;= @StartDate and dt.EndDate &lt;= @EndDate))    
  </code></pre>
  
  <p>only when the </p>
  
  <pre><code>(@ID != 100 AND isAdmin != 1)
  </code></pre>
  
  <p>holds true or else I want to apply the new logic i.e.</p>
  
  <pre><code>(dt.EndDate&gt;sysdatetime()
  </code></pre>
  
  <p>How can I achieve this?</p>