Implementing Paging and Dynamic Sorting on SQL 2005 databases
by mahpour on May.08, 2007, under Uncategorized
ROW_NUMBER() is a new function in SQL 2005 that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
DECLARE @START int;
DECLARE @END int;
DECLARE @Sort nvarchar(100);
SET @Start=10;
SET @END=20;
SET @Sort=’LastName’;
SELECT * FROM (SELECT ROW_NUMBER() over (
ORDER BY CASE
WHEN @Sort=’FirstName’ THEN FirstName
WHEN @Sort=’LastName’ THEN LastName
ELSE FirstName
END) as R ,* From Students) as tbl
WHERE R BETWEEN @Start AND @End
DECLARE @END int;
DECLARE @Sort nvarchar(100);
SET @Start=10;
SET @END=20;
SET @Sort=’LastName’;
SELECT * FROM (SELECT ROW_NUMBER() over (
ORDER BY CASE
WHEN @Sort=’FirstName’ THEN FirstName
WHEN @Sort=’LastName’ THEN LastName
ELSE FirstName
END) as R ,* From Students) as tbl
WHERE R BETWEEN @Start AND @End
