SQL paging sorting

Question

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 @ColSort desc) as RowId
from MyTable
) dt
where RowId BETWEEN @RowIdMin AND @RowIdMax

Answer 1

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

More thorough explanation

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

Answer 2

Dynamic SQL is probably your best bet; swap out the sort options in your row_number() function. You can parameterize the options in the executesql too see msdn

declare @SQLScript nVarchar(4000)

declare @RowIdMin int=10
declare @RowIdMax int=25
declare @ColSort varchar(100)='MyColumn'


Select @SQLScript = 'select * from (select Col1, Col2, ROW_NUMBER() over (order by ' + @ColSort + 'desc) as RowId from MyTable  dt where RowId BETWEEN ' + cast(@RowIdMin as nvarchar(100))+ ' AND ' + cast(@RowIdMax as nvarchar(100))

exec sp_executesql @SQLScript

Answer 3

I totally agree with the other posts, dynamic SQL, or a CASE statement order by are the options to do this as you have described.

However, as an aside, look at the rest of the framework you're using. If its asp.net 3.5 then its built in grids and linq will do all of this for you with very little effort. Try looking at http://www.asp.net/learn.