title: Nested if statements in SQL Server stored procedure SELECT statement
body: <p>I'm new here, and relatively new to stored procedures, so please bear with me! I've checked related questions on here and can't find anything that works in this instance.</p>
  
  <p>I am trying to build a stored procedure (MS SQL Server 2005) that takes a number of passed in values and in effect dynamically builds up the SQL as you would with inline SQL.</p>
  
  <p>This is where I've come unstuck.</p>
  
  <p>We have (somewhat simplified for clarity):</p>
  
  <pre><code>@searchf1 varchar(100),   -- search filter 1
  @searchr1 varchar(100),   -- search result 1
  @searchf2 varchar(100),   -- search filter 2
  @searchr2 varchar(100),   -- search result 2
  @direction char(1),   -- direction to order results in
  AS
  
  set nocount on
  set dateformat dmy
  
  SELECT *
    FROM database.dbo.table T
   WHERE T.deleted = 'n'
  ORDER BY CASE @direction
              WHEN 'A' THEN T.id
              WHEN 'D' THEN T.id DESC
           END
  
  END
  
  set nocount off
  </code></pre>
  
  <p>I have also tried the lines from ORDER BY as:</p>
  
  <pre><code>IF @direction = 'N' THEN
       ORDER BY 
            T.id
  ELSE
       ORDER BY
            T.id DESC
  </code></pre>
  
  <p>Both approaches give me an error along the lines:</p>
  
  <p>"Incorrect syntax near the keyword 'DESC'." (which references the line id DESC following the final ORDER BY</p>
  
  <p>As part of this stored procedure I also want to try to feed in matched pairs of values which reference a field to look up and a field to match it to, these could either be present or ''. To do that I need to add into the SELECT section code similar to:</p>
  
  <pre><code>WHERE 
       deleted = 'n'
       IF @searchf1 &lt;&gt; '' THEN
           AND fieldf1 = @searchf1 AND fieldr1 = @searchr1
  </code></pre>
  
  <p>This however generates errors like:</p>
  
  <p>Incorrect syntax near the keyword 'IF'.</p>
  
  <p>I know dynamic SQL of this type isn't the most elegant. And I know that I could do it with glocal IF ELSE statements, but if I did the SP would be thousands of lines long; there are going to up to 15 pairs of these search fields, together with the direction and field to order that direction on.</p>
  
  <p>(the current version of this SP uses a passed in list of IDs to return generated by some inline dynamic SQL, through doing this I'm trying to reduce it to one hit to generate the recordset)</p>
  
  <p>Any help greatly appreciated. I've hugely simplified the code in the above example for clarity, since it's the general concept of a nested IF statement with SELECT and ORDER BY that I'm inquiring about.</p>