title: DESC and ASC as a parameter in stored procedure
body: <p>I have the following SP that I am using to paginate a list of news articles. As you may be able to guess, <code>@count</code> is the number of rows to return, <code>@start</code> is the index to select rows from (sorted by inner query), <code>@orderby</code> indicates the column to sort by, and <code>@orderdir</code> indicates whether to sort one direction or the other. My original query was 
  <a href="https://stackoverflow.com/questions/10002401/conversion-failed-when-converting-date-and-or-time-from-character-string" title="My original query">here</a>, before I added the <code>@orderdir</code> parameter.</p>
  
  <pre><code>ALTER PROCEDURE [mytable].[news_editor_paginate]
      @count int,
      @start int,
      @orderby int,
      @orderdir int
  AS 
  BEGIN
      SET NOCOUNT ON; 
      SELECT TOP (@count) * FROM 
      (  
          SELECT ne.*,n.publishstate, 
              (CASE WHEN @orderdir = 1 THEN
                  ROW_NUMBER() OVER (
                      ORDER BY                    
                          CASE WHEN @orderby = 0 THEN ne.[time] END DESC,
                          CASE WHEN @orderby = 1 THEN ne.lastedit END DESC,    
                          CASE WHEN @orderby = 2 THEN ne.title END ASC
                      )
              WHEN @orderdir = 2 THEN
                  ROW_NUMBER() OVER (
                      ORDER BY                    
                          CASE WHEN @orderby = 0 THEN ne.[time] END ASC,    
                          CASE WHEN @orderby = 1 THEN ne.lastedit END ASC,
                          CASE WHEN @orderby = 2 THEN ne.title END DESC
                      )
                  END
              ) AS num
              FROM news_edits AS ne
              LEFT OUTER JOIN news AS n
              ON n.editid = ne.id 
          ) 
       AS a
      WHERE num &gt; @start
  END
  </code></pre>
  
  <p>Now nothing actually goes wrong, but the <code>@orderby</code> parameter doesn't work. If provide 1 as the <code>@orderdir</code> parameter, it will give me the exact same results as if I provide 2 as that parameter.</p>