Efficient Store Proc for Paging Very large DataSet using Cursor Approach
anon...@hotmail.com
rowcount, or Subquery Approaches.
This is before the advent of a ranking function from DB such as
ROW_NUMBER() in SQL Server 2005 and the likes of it. So
This one works with SQL2000
What do you think?
==The Generic paging Cursor Approach in Stored Procedure
/*
Generic Paging Routine using Cursor approach.
--------------------------------------------
Built to use with ASPNET custom paging. Just pass the parameters
you your query and it builds the dynamic SQL to return only the
requested page.
This seems to be working for me. I tried the other two paging
approaches (1) Paging By RowCount (which has some errors on sorting)
and (2) Paging by Subquery (which is too slow). This procedure
goes to show that cursors are not necessarily evil at all times.
DON'T FORGET TO HANDLE SQL-INJECTION in your code!
ONE CAVEAT/restriction: Primary key type is set to INT. I normally
use identity column anyway.
NOTE: This returns 2 results: 1 for dataset 1 for the total count
which is useful when consumed by ASPNET or the like.
References:
http://www.thecodeproject.com/aspnet/PagingLarge.asp?select=820618
*/
CREATE PROCEDURE uspPagingCursor (
@Fields VARCHAR(1000) = '*',
@Tables VARCHAR(1000) ,
@PK VARCHAR(100) ,
@PageSize INT,
@PageNumber INT = 1,
@Sort VARCHAR(1000) = '',
@Filter VARCHAR(2000) = '' ,
@Group VARCHAR(1000) = null
)
AS
/*
Find the @PK type
*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int
IF CHARINDEX('.', @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END
/*
This is the part removed from orig code for speed.
I know my @type is INT always.
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
*/
SET @TYPE = ' int '
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS
varchar(50))
/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
SET @strFilter = ' WHERE TRUE '
/* SET @strFilter = '' */
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*Execute dynamic query*/
EXEC(
'DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '
DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup
+ ' ORDER BY ' + @Sort + '
OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ' + @Fields + ' FROM ' + @Tables + ', @tblPK tblPK ' +
@strFilter
+ ' and ' + @PK + ' = tblPK.PK ' + @strGroup + ' ORDER BY '
+ @Sort
)
EXEC('
SELECT (COUNT(' +@Pk +') - 1)/' + @strPageSize + ' + 1 AS PageCount
FROM ' + @tables + @strFilter
)
GO
==Sample Stored Procedure calling the above proc uspPagingCursor
/*************************************************************
Description:
This simply returns page data from tbTransactions Table.
Returns:
Result Set (WHICH web like ASPNET can consume )
Notes:
This invokes the generic paging procedure uspPagingCursor.
*************************************************************/
CREATE PROCEDURE uspGetTransactionsPage
@PageSize INT,
@PageIndex INT = 1,
@SortField VARCHAR(1000) = '',
@QueryFilter VARCHAR(2000) = ''
AS
DECLARE @FieldNames VARCHAR(1000)
DECLARE @TableNames VARCHAR(1000)
DECLARE @PrimaryKey VARCHAR(1000)
DECLARE @JoinExpr VARCHAR(1000)
IF @SortField = '' SET @SortField = 'SubmitDate DESC'
/* an identity/unique column is needed for Paging to work */
SET @PrimaryKey = 'rowid'
SET @TableNames = ' tbTransaction, tbResponseCode , tbUser'
/* Put your SQL SELECT Here */
SET @FieldNames =
'
rowid,
MerchantTransactionId,
MerchantIdProcessor,
TransactionOrigin,
SubmitDate,
ExpirationDate,
TransactionAmount,
CustomerName,
AccountNumber
'
/* Put your SQL SELECT JOIN Here */
SET @JoinExpr =
' tbTransaction.ResponseCode *= tbResponseCode.ResponseCode
and tbTransaction.EmployeeId *= tbUser.userid
'
IF @QueryFilter = ''
SET @QueryFilter = @JoinExpr
ELSE
SET @QueryFilter = @JoinExpr + ' AND ' + @QueryFilter
EXEC uspPagingCursor @FieldNames, @TableNames, @PrimaryKey,
@PageSize, @PageIndex, @SortField, @QueryFilter, NULL
GO