Pasando orden dinámico por en procedimiento almacenado
Estoy creando a continuación el procedimiento almacenado.
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
En lo de arriba cuando paso elOrder by
cláusula dinámicamente, no ordena los datos correctamente, pero cuando escribo el nombre de la columna explícitamente, funciona bien. Podría ser su toma@SortType+' '+@SortDirection
comovarchar
más bien queDate
Intenté escribirOrder by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc
, pero no funcionó
¿Cómo puedo pasar orden por dinámicamente aquí.
Editar: @Andomar: probé la solución provista y agregué un campo más para el tipo de fecha. Y tampoco funcionó.
A continuación es lo que hice.
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