CODEWITHSUNDEEP
sqlsortingpagination
Anthony
Anthony

Reputation: 7300

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

Upvotes: 2

Views: 2626

Answers (3)

Robin Day
Robin Day

Reputation: 102578

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.

Upvotes: 0

u07ch
u07ch

Reputation: 13702

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

Upvotes: 0

Chad Grant
Chad Grant

Reputation: 45422

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

More thorough explanation

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

Upvotes: 2

Related Questions