title: SQL Server Dynamic Order By
body: <p>I'm trying to use a dynamic order by in a stored procedure so I can pass the order I want the data returned into the stored procedure as a parameter. This works fine for VARCHAR fields however if I try to sort an int or datetime field it errors the code I  have is as follows</p>
  
  <pre><code>DECLARE @ORDERBY INT
  SET @ORDERBY = 1
  SELECT TOP 10 * FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT
  ORDER BY 
  CASE 
      WHEN @OrderBy = 1 THEN s10_record_dow
      --WHEN @OrderBy = 2 THEN pk_big_record_id
      else s10_record_dow
  END
  </code></pre>
  
  <p>If I uncomment the second WHEN in the case statement it errors with</p>
  
  <blockquote>
    <p>"Error converting data type varchar to bigint."</p>
  </blockquote>
  
  <p>I can order by this field fine if I dont use the case statement.</p>
  
  <p>Any ideas?</p>