Paging In Sql Server 2000 Store Procedure

You have heard or at least done Paging of the gridview control on the server side. When someone talk about the paging of the data on the sql server side I was surprise how to do the paging of the data on sql server side on in the store procedure which will return data. Then I have start search on doing paging of data on the store procedure side.

Here is piece of code which is used to create the store procedure and the name of the store procedure is GetCustomer. The store procedure will take two parameter one the PageNumber and the second one the PageSize. At the beginning of the store procedure is the declaration of the variable which are used in the store procedure. First is the StartingRow and the EndingRow variable which are used to return the pageSize records by comparing the StartingRow and The EndingRow with the RowNumber of the return result set. Next variable is the TotalRecods which is used to save the total of the records and used to calculate the row number as I have discuss in my last Post, where I have discuss how to calculate row number in sql server 2000 in detail.

ALTER  PROCEDURE [GetCustomer]
 @PageNumber  INT,
@PageSize   INT
AS
Declare @StartingRow  INT,
@EndingRow   INT,
 @TotalRecords INT

Declare @tblTemporary TABLE
(
RowNumber   INT,
CustomerID  VARCHAR(10),
CompanyName NVARCHAR(200),
ContactName NVARCHAR(200),
Country     NVARCHAR(50)
)

Set @TotalRecords =(Select Count(*) from Customers)
Set @EndingRow = @PageNumber * @PageSize
Set @StartingRow = @EndingRow - @PageSize

Insert INTO @tblTemporary
Select @TotalRecords -(Select Count(*) from Customers WHERE Customer.CustomerID < CustomerID) AS RowNumber,
CustomerID,CompanyName,ContactName,Country from Customers AS Customer

Select * from @tblTemporary
Where RowNumber> @StartingRow AND RowNumber <= @EndingRow   

Next is the declaration of the table to save the return record set. Here I have included the required columns which I need to display to user plus the addition column RowNumber, which is used to filter the records based on the StartingRow and EndingRow. After the declaration of the variables next is the assignment statements. In the first of the assignment statement, TotalRow is assign value by selecting the Count(*) from the customer table.Next is to calculate the EndingRow value by multipling the PageNumber with PageSize and at the last assignment statement, calculating the StartingRow by substracting the PageSize from the EndingRow variable.
In the next statement, calculating the Row Number and required columns from the customer table and inserting the returned record in the @tblTemporary table. At the end of the store procedure selecting the records from the tblTemporary table and place the where clause so that the RowNumber of the tblTemporary table will be in between the StartingRow and EndingRow.

Note: Replace the alter keyword at the start of the store procedure with the create keyword so that new store procedure will be created.

The database for this store procedure is the northwind and table is the customer table. After creating the store procedure you can run these commands to see the result

exec getCustomer 1,25
exec getCustomer 2,25

All and any comments / bugs / suggestions are welcomed!