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
FROM MyTable ORDER BY CASE WHEN @ColSort = 'ABC' THEN ABC ....
More thorough explanation