Getting the next page from a store procedure
Here is a the store procedure implementation from "Custom Data Paging in ASP_NET - ASP_NET" article which located on CodeProject
Similar code were added to one of the articles I've just read in MSDN May Magazine.
We can use it with a DataGrid custom paging mechanism in order to get better performance. The default paging mechanism is working only if we bind the grid with a datasource that contain all of our rows for each PageChange event and this is sometimes too expensive.
Below I show another way to get the next page from a store procedures that can yield better performance.
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
CREATE TABLE #TempProducts
(
ID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName varchar(50),
)
-- fill the temp table with all the products for the
-- specified products retrieved from the Products table
INSERT INTO #TempProducts
(
ProductID,
ProductName
)
SELECT
ProductID,
ProductName
FROM Products
-- declare two variables to calculate the
-- range of records to extract for the specified page
DECLARE @FromID int
DECLARE @ToID int
-- calculate the first and last ID of the range of topics we need
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
-- select the page of records
SELECT ProductID, ProductName FROM #TempProducts
WHERE ID >= @FromID AND ID <= @ToID
GO
What I don't like with this approach is that its using an Insert-Select statement even though we need only the amount of rows that are part of the next page. We know that Insert statements are resources consumers so I will try to avoid it when its not necessary.
A better, scalable approach for store procedure that will retrieve the next page will be as follow:
By using a cursor and an helper table we only insert the rows we need and skip the one we don't need.
Create Procedure GetProductPage
@JobId varchar(36), --guid
@CurrentPage As tinyint,
@PageSize As tinyint,
@TotalRows As int OUTPUT
-- Declare variables.
Declare @FirstRow int
Declare @LastRow int
Declare @ProductId int
Declare @ProductRowsCount int
-- Initialize variables.
Set @FirstRow = (@CurrentPage - 1) * @PageSize
Set @LastRow = (@CurrentPage * @PageSize + 1)
Set @ProductCount = 0
declare rows_cursor CURSOR for
select ProductId from Products
WHERE ......... ORDER BY ProdcutName
OPEN rows_cursor
FETCH NEXT FROM rows_cursor INTO @CurrentProductId
WHILE @ProductCount <= @LastRow AND @@FETCH_STATUS = 0
BEGIN
IF @ProductRowsCount >= @FirstRow AND @ProductRowsCount <= @LastRow
BEGIN
--KEEP THE PRODUCT ID OF THE ROW INTO THE HELPER TABLE
INSERT INTO ProductHelperTable (JobId, EntityId) VALUES (@JobId, @CurrentProductId)
END
SET @ProductRowsCount = @ProductRowsCount + 1
if @@FETCH_STATUS = 0
FETCH NEXT FROM rows_cursor INTO @CurrentProductId
END
CLOSE rows_cursor
DEALLOCATE rows_cursor
SELECT *
FROM Products, HelperTable
WHERE HelperTable.JobId = @JobId AND
Products.ProductId = HelperTable.EntityId AND
............
--Return the total number of records available as an output parameter
Select @TotalRows = Count(*)
select ProductId
FROM Products
WHERE .........
ORDER BY ProductName