Code Monkey

Typically when I use a stored procedure to return tabular data, I pass in a sort flag.  I have found the easiest way to do that sort is with a case statement.  I typically have the columns have a numeric value and pass that on click, where is what I get combined with paging the results.

–these would be passed into the stored procedure
 Declare @sortby int
set @sortby = 4
Declare @pagenum int
set @pagenum = 1
Declare @pagesize int
set @pagesize = 50

 Declare @pagestart int;
Declare @pagestop int;
set @pagestart = (@pagenum -1) * @pagesize;
set @pagestop = @pagestart + @pagesize;

;With Final as (Select *, ROW_NUMBER() over (Order By
     Case When @sortby = 1 THEN taskid End ASC,
     Case When @sortby = 2 THEN taskid End Desc,
     Case When @sortby = 3 THEN tasktitle End ASC,
     Case When @sortby = 4 THEN tasktitle End Desc
) as Row From Tasks)
Select * From Final where row > @pagestart and row <= @pagestop;