title: Can I store SQL Server sort order in a variable?
body: <p>I have the following SQL within a stored procedure. Is there a way to remove the IF statement and pass the 'ASC'/'DESC' option as a variable?</p>
  
  <p>I know I could do the query a number of different ways, or return a table and sort it externally etc. I would just like to know if I can avoid duplicating the CASE statement.</p>
  
  <pre><code>IF @sortOrder = 'Desc'
    BEGIN
      SELECT * FROM #t_results
      ORDER BY
      CASE WHEN @OrderBy = 'surname'           THEN surname END DESC,
      CASE WHEN @OrderBy = 'forename'          THEN forename END DESC,
      CASE WHEN @OrderBy = 'fullName'          THEN fullName END DESC,
      CASE WHEN @OrderBy = 'userId'            THEN userId END DESC,
      CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
      CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
      CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
      CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
      CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
      CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC 
      END
   ELSE
     BEGIN
      SELECT * FROM #t_results
      ORDER BY
      CASE WHEN @OrderBy = 'surname'           THEN surname END DESC,
      CASE WHEN @OrderBy = 'forename'          THEN forename END DESC,
      CASE WHEN @OrderBy = 'fullName'          THEN fullName END DESC,
      CASE WHEN @OrderBy = 'userId'            THEN userId END DESC,
      CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
      CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
      CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
      CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
      CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
      CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC 
      END
  END
  </code></pre>