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