Return a Subset of Data from a Table
The ability to page in SQL Server is one of those things that everyone wants, but can't quite seem to get from Microsoft. Many ideas have been posted, each claiming to be _the_ way to do it. In the spirit of mine is better than yours, I've implemented my own paging scheme.
A feature that is in the upcoming version of SQL Server is the ability to supply a variable to the TOP keyword. Well, as it turns out, this functionality does exist in SQL Server 2000. The only difference is the syntax. To limit the number of rows returned from a query based on a variable is to use SET ROWCOUNT @Foo. Don't forget, however, to reverse that after the query by setting ROWCOUNT to 0.
The principle of which this works is that I return all of the primary keys in a table into a table datatype up to and including the record that i want to start at. Then I grab that value by selecting the TOP 1 from that temp table, ordering by the value, descending. Now I have the starting primary key value.
At this point, it's a matter of looping through the records, row by row until I have filled my cache table with a single page's worth of data. Once that is complete, I return the contents of the cache table. In addition, in the form of an OUTPUT param, I return the total number of rows in the table. I have this value as a result of a sanity check that stops the proc if we are requesting a page of data that is past the end of the table.
I hope this sproc finds you well.
-- =============================================
-- Create procedure with OUTPUT Parameters
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'GetCustomerPage'
AND type = 'P')
DROP PROCEDURE GetCustomerPage
GO
CREATE PROCEDURE GetCustomerPage
@PageNumber int = 1,
@RowsPerPage int = 20,
@TableRowCount int OUTPUT
AS
-- declare tracking variables
DECLARE @RowCount int
DECLARE @StartRecord int
DECLARE @EndRecord int
-- initialize tracking values
SET @StartRecord = ((@PageNumber * @RowsPerPage) - @RowsPerPage) + 1
SET @RowCount = 0
-- fetch total number of rows in table
SET @TableRowCount = (SELECT COUNT(CustomerID) FROM Customers)
-- return immediately if there will be
-- no rows to return
IF @StartRecord > @TableRowCount
RETURN
-- create temp PK table
DECLARE @PrimaryKey TABLE (PK nchar(5) PRIMARY KEY)
-- return rows up to an including the start record
SET ROWCOUNT @StartRecord
INSERT INTO @PrimaryKey (PK)
SELECT CustomerID FROM Customers ORDER BY CustomerID
-- reset rowcount
SET ROWCOUNT 0
-- obtain starting PK value
-- it is the highest PK in the table
DECLARE @id nchar(5)
SET @id = (SELECT TOP 1 PK FROM @PrimaryKey ORDER BY PK DESC)
-- replicate table structure
SELECT TOP 0 * INTO #Customers FROM Customers
-- enable the following if the PK of the table
-- is an identity column. the customers table
-- in the Northwinds database does not have an
-- autoincrementing int value for a PK. it is
-- an nchar(5).
--SET IDENTITY_INSERT #Customers ON
-- if row is within page boundaries, cache row
WHILE @RowCount < @RowsPerPage
BEGIN
-- cache row
INSERT INTO #Customers ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM Customers WHERE CustomerID = @id
-- advance pseudo-cursor to next highest PK
SET @id = (SELECT MIN(CustomerID) FROM Customers WHERE CustomerID > @id)
-- advance row count
SET @RowCount = @RowCount + 1
END
-- return cached rows
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM #Customers
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE @TableRowCount_local int
exec dbo."GetCustomerPage" 1, 20, @TableRowCount = @TableRowCount_local OUTPUT
PRINT @TableRowCount_local
GO
