How can I do server side pagination of results in SQL Server 2000?
In SQL Server 2005, there is a feature called row_number() which makes pagination very simple.
SELECT * FROM table WHERE row_number() between x and y
Is there any SQL server way to do the same thing in SQL Server 2000?
(Note: I don’t have access to a unique sequential numbering for the query, i.e. something which would be a synonym for row_number())
3 Solutions collect form web for “How can I do server side pagination of results in SQL Server 2000?”
SELECT * FROM ( SELECT TOP (Y - X ) * FROM ( SELECT TOP Y * FROM mytable ORDER BY column ) q ORDER BY column DESC ) ORDER BY column
Not sure if this is the most elegant solution, but it worked for me when we used SQL 2000…
If you’re writing a stored procedure, you can do it with a temporary table.
Create a temporary table which has an automatically incrementing Identity column as well as the same columns as your result set.
Then, select the data from the result set and insert it into this temporary table (in the right order).
Finally, return results from your temporary table where the value of your Identity column acts as your row number.
You can also use cursor for this.
DECLARE @i INT DECLARE C CURSOR FOR SELECT ... FROM ... ORDER BY ... OPEN C FETCH ABSOLUTE @StartRow FROM C SET @i = 1 WHILE (@@FETCH_STATUS == 0) AND (@i < @EndRow - @StartRow) BEGIN -- Do whatever you need FETCH NEXT FROM C END CLOSE C DEALLOCATE C
The only problem here is that each row is returned as a separate result set, but it does the job.