Sql Paging/Sorting - Efficent method with dynamic sort?

Question

I'm trying to implement this style of paging:

http://www.4guysfromrolla.com/webtech/042606-1.shtml

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName 
FROM employees e
INNER JOIN Departments D ON
   e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO 

This method works great, however, is it possible to use this method and have dynamic field sorting?

If we change this to

SELECT e.*, d.name as DepartmentName 
FROM employees e
INNER JOIN Departments D ON
   e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.FirstName DESC

It breaks the sorting...

Is there any way to combine this method of paging with the ability to sort on different fields?

Answer 1

You would need to keep track of both the first name and ID using this method. The reason you have to keep track of both is that first name may not be unique in your data set, so you order by it and a unique value. The order by in the select for the first record must match the order by in the select for the recordset.

Untested, but you should get the idea. You'll need to declare @first_first_name as a variable similar to what was done with @first_id.

DECLARE @first_first_id VARCHAR(50) -- whatever your first name field size is
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID, @first_first_name = FirstName FROM employees ORDER BY FirstName DESC, employeeid

SELECT e.*, d.name as DepartmentName 
FROM employees e
INNER JOIN Departments D ON
   e.DepartmentID = d.DepartmentID
WHERE
    e.FirstName <= @first_first_name
    OR (e.FirstName = @first_first_name AND employeeid >= @first_id)
ORDER BY e.FirstName DESC, employeeid