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

Rate

Share

Share

Rate