title: Passing dynamic order by in stored procedure
body: <p>I am creating below stored procedure.</p>
  
  <pre><code>declare @PageNum as Int
  declare @PerPageResult as Int
  declare @StartDate as varchar(25)
  declare @EndDate as varchar(25)
  declare @SortType as Varchar(50)
  declare @SortDirection as Varchar(4)
  set @PageNum=1
  set @PerPageResult=20
  set @StartDate='2008-02-08'
  set @EndDate='2015-02-08'
  set @SortType='RegDate'
  set @SortDirection='Desc'
  declare @Temp Table(RowNum int, RegDate Date, Registered int, Female int, Male int, [Join] int, Rebill int, TotalPointsEarned int, Expire int)
  declare @sort varchar(50)
  Insert into @Temp
      Select ROW_NUMBER() over (order by @SortType+' '+@SortDirection) As RowNum, * From (    
      SELECT 
      CAST(m.registrationdate AS Date) as RegDate,    
      count(m.id) Registered,
      count(CASE WHEN m.gender='F' then 'F' end) As Female,
      count(CASE WHEN m.gender='M' then 'M' end) As Male
      count(CASE WHEN p.paymenttransactiontype='N' then 'N' end) As [Join],
      count(CASE WHEN p.paymenttransactiontype='R' then 'R' end) As Rebill,
      count(m.tokensearned) As TotalPointsEarned,
      count(CASE WHEN p.paymenttransactiontype='E' then 'E' end) As Expire
      from member m
      join payment p on m.id=p.id_member
      join user_role u on u.member_id=m.id
      where u.role_id &lt;&gt; 3
      and CAST(m.registrationdate AS Date) &gt; @StartDate and CAST(m.registrationdate AS Date) &lt; @EndDate
      GROUP BY CAST(m.registrationdate AS Date)
      ) as aa 
      Select * from @Temp Where RowNum&gt;((@PageNum-1)*@PerPageResult) and RowNum&lt;=@PerPageResult * @PageNum
      Order by @SortType+' '+@SortDirection
  </code></pre>
  
  <p>In above when i pass the <code>Order by</code> clause dynamically, its not sorting the data properly but when i write column name explicitly, it works fine. Might be its taking <code>@SortType+' '+@SortDirection</code> as <code>varchar</code> rather than <code>Date</code></p>
  
  <p>I tried writing <code>Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc</code>, but it didn't work </p>
  
  <p>How can i pass order by dynamically here.</p>
  
  <p>Edit:
  @Andomar: I tried your provided solution and added one more field for Date type. And it didn't work too.</p>
  
  <p>below is what i did.</p>
  
  <pre><code>create table t1 (id int, name varchar(50), dt date);
  insert t1 values 
      (1, 'Chihiro Ogino','2009-02-08'), 
      (2, 'Spirit of the Kohaku River','2008-02-08'), 
      (3, 'Yubaba','2012-02-08');
  
  
  
  declare @sortColumn varchar(50) = 'dt'
  declare @sortOrder varchar(50) = 'ASC'
  
  select  *
  from    t1
  order by
          case
          when @sortOrder &lt;&gt; 'ASC' then 0
          when @sortColumn = 'id' then id
          end ASC
  ,       case
          when @sortOrder &lt;&gt; 'ASC' then ''
          when @sortColumn = 'name' then name
          end ASC
  ,       case
          when @sortOrder &lt;&gt; 'ASC' then ''
          when @sortColumn = 'dt' then name
          end ASC
  ,       case
          when @sortOrder &lt;&gt; 'DESC' then 0
          when @sortColumn = 'id' then id
          end DESC
  ,       case
          when @sortOrder &lt;&gt; 'DESC' then ''
          when @sortColumn = 'name' then name
          end DESC
  ,       case
          when @sortOrder &lt;&gt; 'DESC' then ''
          when @sortColumn = 'dt' then name
          end DESC
  </code></pre>