在存储过程中传递动态顺序 [英] Passing dynamic order by in stored procedure
问题描述
我正在创建以下存储过程.
I am creating below stored procedure.
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
在上面,当我动态传递 Order by
子句时,它没有正确排序数据,但是当我明确写入列名时,它工作正常.可能是将 @SortType+' '+@SortDirection
作为 varchar
而不是 Date
In above when i pass the Order by
clause dynamically, its not sorting the data properly but when i write column name explicitly, it works fine. Might be its taking @SortType+' '+@SortDirection
as varchar
rather than Date
我尝试编写Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc
,但是没有用
I tried writing Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc
, but it didn't work
我如何在这里动态传递订单.
How can i pass order by dynamically here.
@Andomar:我尝试了您提供的解决方案,并为日期类型添加了一个字段.它也不起作用.
@Andomar: I tried your provided solution and added one more field for Date type. And it didn't work too.
下面是我做的.
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
推荐答案
您可以使用复杂的 order by
子句.对于每个排序方向和每个数据类型,这需要一个 case
.使用此示例数据集:
You can use a complicated order by
clause. That requires one case
for each sort direction and each data type. With this example dataset:
create table t1 (id int, name varchar(50), created date);
insert t1 values
(1, 'Chihiro Ogino', '2012-01-01'),
(2, 'Spirit of the Kohaku River', '2012-01-03'),
(3, 'Yubaba', '2012-01-02');
您可以使用 order by
子句,例如:
You could use an order by
clause like:
declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'
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 cast(null as date)
when @sortColumn = 'created' then created
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 cast(null as date)
when @sortColumn = 'created' then created
end DESC
另一种选择是动态创建查询,并使用 exec
运行它.例如:
Another option is to create the query dynamically, and run it with exec
. For example:
declare @sql nvarchar(max)
set @sql = 'select * from YourTable order by ' + @sortColumn + ' ' + @sortDir
exec (@sql)
这篇关于在存储过程中传递动态顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!