Breaking a table into pages

				
--**************************************
-- Name: Breaking a table into pages
-- Description:Reading the paging technique writen by Marcio Coleho here on PSC, I found a little flaw in the method.
Althought the code did break the table into pages, it didn't bring them in the correct order, if you want to use other field than PK.
I fixed this little flaw, but it still need a <B style="color:red">SINGLE</B> <B>PRIMARY</B> or <B>ALERNATE</B> KEY.
The original Coelho's code can be found at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=652&lngWId=5
-- By: MstrControl
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=656&lngWId=5--for details.--**************************************

DROP PROCEDURE sp_Table_Paging 
go 
CREATE PROCEDURE sp_Table_Paging 
	@Page 			int, 
	@RecsPerPage 	int, 
	@Fields			varchar(2000),
	@Table 			sysname,
	@Key 			sysname
AS 
/*
exec sp_Table_Paging 1, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 2, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 3, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 4, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 5, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 6, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 7, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 8, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
exec sp_Table_Paging 9, 100, 'CustomerID, CompanyName', 'Customers', 'CompanyName'
*/
DECLARE	@previousRows 	int, 
		@rowsReviewed 	int, 
		@sql 			varchar(2000) 
SET @rowsReviewed = @RecsPerPage * @page 
SET @previousRows = @rowsReviewed - @RecsPerPage 
IF (@rowsReviewed = @RecsPerPage) 
	-- First page is requested 
BEGIN 
	SELECT @sql = ''
	SELECT @sql = @sql + ' select top ' + CONVERT(varchar, @RecsPerPage) + ' ' + @Fields 
	SELECT @sql = @sql + ' from ' + @Table 
	SELECT @sql = @sql + ' ORDER BY ' + @Key + ' asc ' 
	EXEC(@sql) 
END 
ELSE 
BEGIN 
	SELECT @sql = ''
	SELECT @sql = @sql + ' select top ' + CONVERT(varchar, @RecsPerPage) + ' ' + @Fields 
	SELECT @sql = @sql + ' from ' + @Table 
	SELECT @sql = @sql + ' where ' + @Key + ' in ' 
	SELECT @sql = @sql + ' 				(select top ' + CONVERT(varchar, @rowsReviewed) + @Key 
	SELECT @sql = @sql + '				from ' + @Table 
	SELECT @sql = @sql + '				 ORDER BY ' + @Key + ' asc )' 
	SELECT @sql = @sql + 'AND ' + @Key + ' not in ' 
	SELECT @sql = @sql + ' 				(select top ' + CONVERT(varchar, @previousRows) + @Key
	SELECT @sql = @sql + '				from ' + @Table 
	SELECT @sql = @sql + '				 ORDER BY ' + @Key + ' asc )' 
	SELECT @sql = @sql + ' ORDER BY ' + @Key + ' asc ' 
	EXEC(@sql) 
END
Go