Anthony Anthony - 27 days ago 9
SQL Question

SQL paging sorting

I need to display a grid on a webpage. The data will come from SQL Server 2008 through a stored procedure. As the sproc returns thousands of records, I decided to go for a paging option which works fine. In the stored procedure, I do something like this:

declare @RowIdMin int=10
declare @RowIdMax int=25

select *
from (select Col1, Col2, ROW_NUMBER() over (order by Col1 desc) as RowId
from MyTable ) dt
where RowId BETWEEN @RowIdMin AND @RowIdMax


This works fine as long as the user is happy to get the data sorted by Col1. How could I rewrite this if I don't know in advance which column the recordset has to be sorted by? This doesn't work:

declare @RowIdMin int=10
declare @RowIdMax int=25


declare @ColSort varchar(100)='MyColumn'

select *
from (select Col1, Col2, ROW_NUMBER() over (order by <b>@ColSort</b> desc) as RowId
from MyTable) dt
where RowId BETWEEN @RowIdMin AND @RowIdMax

Answer Source

FROM MyTable ORDER BY CASE WHEN @ColSort = 'ABC' THEN ABC ....

More thorough explanation

http://www.extremeexperts.com/sql/articles/CASEinORDER.aspx