Hi,
This is a generic SQL Server 2000 stored procedure that will perform pagination on any table. The stored procedure accepts the name of the table, the columns to output (defaults to all columns in the table), an optional WHERE condition, an optional sort order, the page number to retrieve and the number of rows per page.
CREATE PROCEDURE [dbo].[GetPage]
@pTableName VARCHAR(30),
@pColumns VARCHAR(200) = '*',
@pFilter VARCHAR(200) = '',
@pSort VARCHAR(200) = '',
@pPage INT = 1,
@pPageRows INT = 10
AS
SET NOCOUNT ON
DECLARE @vSQL VARCHAR(4000)
DECLARE @vTempTable VARCHAR(30)
DECLARE @vRowStart INT
DECLARE @vTotalRows INT
SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) +
CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3))
SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName
IF @pFilter != '' AND @pFilter IS NOT NULL
SET @vSQL = @vSQL + ' WHERE ' + @pFilter
IF @pSort != '' AND @pSort IS NOT NULL
SET @vSQL = @vSQL + ' ORDER BY ' + @pSort
EXECUTE (@vSQL)
-- Get the total number of rows selected
SET @vTotalRows = @@ROWCOUNT
-- If page number = 0, set it to the first page
IF @pPage = 0
SET @pPage = 1
-- If page number is beyond the last page, set page to the last page
IF (@pPage * @pPageRows) > @vTotalRows
BEGIN
SET @pPage = @vTotalRows / @pPageRows
IF (@vTotalRows % @pPageRows) != 0
SET @pPage = @pPage + 1
END
SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1
SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) +
' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID'
EXECUTE (@vSQL)
SET @vSQL = 'DROP TABLE ' + @vTempTable
EXECUTE (@vSQL)
GO
Here's a few examples on how to use it using the Northwing database:
EXECUTE [dbo].[GetPage] 'Customers', '*', '', '', 1, 10
EXECUTE [dbo].[GetPage] 'Customers', '*', '', 'CustomerID DESC', 1, 10
To confirm, this is not my work but is courtesy of
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1055
Cheers, John