MS SQL Tips & Tricks

How to do custom pagging using SQL queries

To do simple record paging we can use same adventages new to MS SQL e.g. ROW_NUMBER() function. We just need to order the records and and the row number divide by the amount of rows we want to have on a single page. That is emougtht to know which record is placed on which page. Next we do simple select of records placed on the poage we want to get... 

CREATE PROCEDURE [dbo].GetPaggedEmployees
	@cRowsPerPage    int = 10,
	@cReturnPage     int = 1
AS BEGIN

	select *,CEILING(
		( Row_NUMBER() over(order by Forename) /
		(@cRowsPerPage*1.0))
		) AS PageNo
	into #TEMP_PAGE
	from Employees

	select * from #TEMP_PAGE where PageNo=@cReturnPage

	RETURN(0);
END;
go

and execution example. We need to see the 3rd page of 10 records

exec [dbo].GetPaggedEmployees
	@cRowsPerPage    = 10,
	@cReturnPage     = 3

We can't used ROW_NUMBER() in WHERE clause that is why I've used temporary table. But of course you can use also table variable, but you need to know already the structure of result table. Subquery can be used either but it shoud not be to much difftence to temporary table.

See also


Learn more tricks

Add Comment

Comments