Tworzę poniższą procedurę przechowywaną.

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 <> 3
    and CAST(m.registrationdate AS Date) > @StartDate and CAST(m.registrationdate AS Date) < @EndDate
    GROUP BY CAST(m.registrationdate AS Date)
    ) as aa 
    Select * from @Temp Where RowNum>((@PageNum-1)*@PerPageResult) and RowNum<=@PerPageResult * @PageNum
    Order by @SortType+' '+@SortDirection

W powyższym przypadku, gdy zdajęOrder by klauzula dynamicznie, nie sortuje poprawnie danych, ale gdy jawnie piszę nazwę kolumny, działa dobrze. Może to być jego przyjęcie@SortType+' '+@SortDirection tak jakvarchar zamiastDate

Próbowałem pisaćOrder by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc, ale to nie zadziałało

Jak mogę dynamicznie przekazać zamówienie tutaj.

Edytuj: @Andomar: wypróbowałem podane rozwiązanie i dodałem jeszcze jedno pole dla typu Data. I to też nie zadziałało.

poniżej to, co zrobiłem.

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 <> 'ASC' then 0
        when @sortColumn = 'id' then id
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'name' then name
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'dt' then name
        end ASC
,       case
        when @sortOrder <> 'DESC' then 0
        when @sortColumn = 'id' then id
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'name' then name
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'dt' then name
        end DESC