How to use dynamic 'order by' in a table-valued function

Question

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
)

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.

Do you want to order your result set?

Evgeny
– Evgeny

2017-04-12 11:27:43 +00:00
Commented Apr 12, 2017 at 11:27 — Evgeny
– Evgeny, Commented Apr 12, 2017 at 11:27

cloudsafe · Accepted Answer · 2017-04-12 11:38:14Z

You have to cover all the column names hard-coded:

ALTER FUNCTION [dbo].[fn_Get_NormalOrders]
(   
    @minimalLevel int,
    @recordStart int,
    @recordsEnd int,
    @orderBy varchar(30)
)
RETURNS TABLE 
AS
RETURN 
(
    select * 
        from
        (
            select case @orderBy
                    when 'Column1' then ROW_NUMBER() over (order by Column1) 
                    when 'Column2' then ROW_NUMBER() over (order by Column2) 
                    when 'Column3' then ROW_NUMBER() over (order by Column3) 
                    when 'Column4' then ROW_NUMBER() over (order by Column4)
                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 <= @minimalLevel
        ) as x
    where x.row between @recordStart and @recordsEnd
)

Gordon Linoff · Accepted Answer · 2017-04-12 11:37:11Z

I'm pretty sure you cannot do this easily. One method is a case:

. . .
order by (case when @OrderBy = 'col1' then col1 end),
         (case when @OrderBy = 'col2' then col2 end),
         . . .

(Note there is a separate case for each possibility. This prevents type conflicts in the values used for sorting.)

Unfortunately, a parameter cannot replace a column name or expression. That would be the simplest solution.

Another possibility that doesn't work is dynamic SQL (the "varchar" approach you mention). Unfortunately, dynamic SQL is not allowed in SQL Server.

There is a very complicated method, where the function uses xp_cmdshell to get around these limits. That is probably not worth the effort for this purpose.

The limitation on exec is phrased by specifying that EXECUTE can only be used for extended stored procedures:

EXECUTE statements calling extended stored procedures.

Collectives™ on Stack Overflow

How to use dynamic 'order by' in a table-valued function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related