如何在表值函数中使用动态'order by' [英] How to use dynamic 'order by' in a table-valued function

问题描述

我使用表值函数作为参数化视图。几乎一切都有效，除了按部分的顺序。我把原来的功能改成了这个短的。



功能：

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屋！