如何在表值函数中使用动态'order by' [英] How to use dynamic 'order by' in a table-valued function
本文介绍了如何在表值函数中使用动态'order by'的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用表值函数作为参数化视图。几乎一切都有效,除了按部分的顺序。我把原来的功能改成了这个短的。
功能:
I use a table-valued function as a parametered view. Almost everything works, except the order by part.I altered the original function into this short one.
The function:
ALTER FUNCTION [dbo].[fn_Get_NormalOrders]
(
@minimalLevel int,
@recordStart int,
@recordsEnd int,
@orderBy varchar(30)
)
RETURNS TABLE
AS
RETURN
(
select *
from
(
select
ROW_NUMBER() over (order by @orderBy) as row
,d.nameModel
,t.idToner
from toner t
inner join vwWebDevice d on d.idDevice = t.idDevice
and d.statusDevice not like '%stale%'
and isnull(d.deleted,0) = 0
inner join groups g on g.idGroup = d.idGroup
where
t.currentLevel <= @minimalLevel
) as x
where x.row between @recordStart and @recordsEnd
)
我尝试过:
我找到了ma的解决方案查看此查询的varchar并执行此操作,但如何返回结果?当我现在使用它时,结果总是相同的。
What I have tried:
I found a solution by making a varchar of this query and execute this, but how do i return te result? When i use it as it is now, than the result is always the same.
推荐答案
我想通过PHP webportal按列对结果进行排序。起初我在脚本中有一个繁重的查询(我对这个主题做了很短的介绍),但我想把重型逻辑放在SQL服务器上。像一个有很多逻辑的视图。除了表格排序(以及非常重要的分页)之外,我得到了所有内容。
我找到了解决方案(有一点帮助:-))。我没有将查询转换为文本,而是改变了sting而不是执行它,我使用了一个案例。之后我不得不添加一个额外的排序因为结果没有在行上排序。
我现在可以使用如下函数:
I want to sort the result by column via a PHP webportal. At first i had a heavy query in the script (i made it very short for this topic), but i wanted to put the heavy logic on the SQL server. Something like a view with lots of logic in it. I got everything in except the table sort (and also the very important paging).
I found the solution ( with a little bit of help :-) ). Instead of convert the query to text, than alter the sting and than excute it, i used a case. I had to add a extra sort afterwards because the result was not sorted on the row.
I can now use the function like:
select * from dbo.fn_Get_NormalOrders(40,5,30,'nameModel') order by row
select *
from
(
select
case @orderBy
when 'nameModel' then ROW_NUMBER() over (order by d.nameModel)
when 'idToner' then ROW_NUMBER() over (order by t.idToner)
else ROW_NUMBER() over (order by t.DTE)
end as [row]
,d.nameModel
,t.idToner
from toner t
inner join vwWebDevice d on d.idDevice = t.idDevice
and d.statusDevice not like '%stale%'
and isnull(d.deleted,0) = 0
inner join groups g on g.idGroup = d.idGroup
where
t.currentLevel <= 20
) as x
where x.row between 1 and 20
这篇关于如何在表值函数中使用动态'order by'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文