title: Switch case/If in a where clause
body: <p>I am trying to change the conditions of PART of a where based on some value. I have looked at similar articles on here including:</p>
  
  <p><a href="https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause">SQL Switch/Case in where clause</a></p>
  
  <p>But they aren't quite dealing with my case.</p>
  
  <p>What I have is a stored procedure that does a select based on its inputs. Those inputs are optional (or have default parameters as they prefer to call it).</p>
  
  <p>2 of said parameters are numbers.</p>
  
  <ul>
  <li>if the first number only is specified then column X must equal that number.</li>
  <li>if the first and second number is specified then column X must be >= to first AND &lt;= to second.</li>
  <li>if only the second number is specified it is INVALID.</li>
  </ul>
  
  <p>This is what I tried (which obviously didn't work)</p>
  
  <pre><code>DECLARE @SECOND INT;
  DECLARE @FIRST INT;
  
  SET @FIRST = 123456;
  SET @SECOND = 67890;
  
  SELECT * FROM BANK_DETAIL 
  WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES
      CASE
          WHEN @SECOND IS NULL THEN 
              X = @FIRST
          ELSE 
              X &gt;= @FIRST AND X &lt;= @SECOND 
      END
          -- POSSIBLY SOME MORE WHERE CLAUSES
  ORDER BY X
  </code></pre>
  
  <p><em>REALLY</em> this feels like it needs an IF/ELSE rather than a CASE but I was directed towards CASE.....</p>
  
  <p>Oh, this is MS SQL >= 2005</p>