Pagination through SQL Script

,

Pagination of the records helps in faster and better display while generating a huge report or displaying hundreds of records on the screen. Performing the pagination through SQL script is some what tricky and an average developer finds it difficult to implement. Here I am suggesting the script to resolve the pagination problem.

Use this script to get page wise display of the output. The inputs are the no of rows per page & the page no you wish to display. The same script can be applied in Northwind database to get the feeling of page wise display.

CREATE proc usp_OrderReport(
	@PgNo		int,		-- Page No to be displayed
	@RowPerPg	int)		-- No of Rows Per page
/*
script name	: usp_OrderReport
author		: Tapan K Sahu
author's email  : tap_ks@yahoo.com
description	: Getting Pagewise Report of Orders places
exec usp_OrderReport  3, 10
*/
as
Begin
	declare @sql	nvarchar(4000)

	select	@sql = 'select top ' + cast(@RowPerPg as varchar) + ' Y.OrderId, Y.CustomerId, Y.EmployeeId from (select distinct top ' +
		cast(@RowPerPg * @PgNo as varchar) + ' X.OrderId, X.CustomerId, X.EmployeeId from Orders X where X.OrderId not in (select top ' +
		cast(@RowPerPg * (@PgNo - 1) as varchar) + ' OrderId from ' +
		'Orders Order By OrderId) Order BY X.OrderId) Y Order By Y.OrderId'

	print @sql
	exec sp_executesql  @sql

end

Rate

3 (1)

Share

Share

Rate

3 (1)