SQL Server Paging Grand Tourismo!

				
--**************************************
-- Name: SQL Server Paging Grand Tourismo!
-- Description:This is a stored procedure that will allow you to page through a result set that has been generated with arbitrary SQL. This code is generic and will allow paging of ANY SQL statement except for execute statements. So, basically, ALL selects will work. It doesnt matter, the procedure uses server metadata to generate a temp table containing just the page you want, which it then returns. It seems reasonable fast, I have a table with 200,000 rows, and I can retrieve any page of data in less than 2 seconds.
-- By: Andrew Carrasco
--
-- Inputs:@page_num --> The page you want to retrieve, @rows_per_page --> The number of rows in a page,
@total_rows (OUTPUT) --> The domain of the query,
@sql_string --> The SQL you want to execute
--
-- Returns:A resultset containing just the rows you selected.
--
-- Assumes:This doesnt execute 'execute' statements, and doesnt work against other stored procs. It ONLY works with arbitrary SQL SELECT statements. Still, it is rather useful. Call it from your other stored procs. It doesnt check to see if you are at the end of the pages. If you request a page that is off the end of the result set you will just get an empty result set. Run a count() on your select statements and divide it by the number of rows per page to get the Page count.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=718&lngWId=5--for details.--**************************************

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
ALTER procedure sp_Page_Data 
	@page_num int = 1,
	@rows_per_page int = 25,
	@total_rows int output,
	@sql_string varchar(4096)
as
set nocount on
begin
DECLARE @column_name VARCHAR(100)
DECLARE @column_num INT
DECLARE @column_count INT
DECLARE @declaration_String VARCHAR(1000)
DECLARE @cursor_variables VARCHAR(1000)
DECLARE @html_cursor_variables VARCHAR(1000)
DECLARE @column_size int
DECLARE @column_type_name varchar(100)
DECLARE @max_page int
DECLARE @rows_on_last_page int
 SELECT @declaration_String = ' '
 SELECT @cursor_variables = ' '
 SELECT @html_cursor_variables = ''
declare @starting_record int
set @starting_record = ((@page_num * @rows_per_page) - @rows_per_page) + 1
exec('declare page_cursor scroll cursor for ' + @sql_string)
open page_cursor
set @total_rows = @@CURSOR_ROWS
CREATE TABLE #cols
(ColNum INT IDENTITY (1, 1) NOT NULL ,
ColName VARCHAR(100),
ColCreateString VARCHAR(100)
)
DECLARE colCur CURSOR FOR SELECT A.column_name, A.ordinal_position, B.column_count, 
	A.column_size, C.[name] As Type_Name
	FROM master.dbo.syscursorcolumns A, master.dbo.syscursors B, master.dbo.systypes C
	WHERE A.cursor_handle = B.cursor_handle
	AND A.data_type_sql = C.xtype
	AND B.cursor_name = 'page_cursor'
	ORDER BY ordinal_position
OPEN colCur 
FETCH NEXT FROM colCur INTO @column_name, @column_num, @column_count, @column_size, @column_type_name
INSERT INTO #cols( ColName, ColCreateString) VALUES ( @column_name, @column_type_name + '(' + CONVERT(VARCHAR, @column_size) + ')')
DECLARE @temp_table_string VARCHAR(4096)
DECLARE @temp_col_string VARCHAR(2000)
DECLARE @temp_declare_string varchar(4096)
SET @temp_declare_string = ' '
SET @temp_table_string = 'CREATE TABLE #converge ('
WHILE (@@FETCH_STATUS = 0)
BEGIN
	---INSERT COLUMN NAMES INTO THE cols TABLE
	INSERT INTO #cols( ColName, ColCreateString) VALUES ( @column_name, @column_type_name + '(' + CONVERT(VARCHAR, @column_size) + ')')
	IF ( @column_num +1) < @column_count 
	BEGIN
		 SELECT @cursor_variables = @cursor_variables + ' @' + @column_name + ', '
		 SELECT @temp_col_string =
			CASE @column_type_name
				WHEN 'varchar' THEN @column_name + ' ' + @column_type_name + '(' + CONVERT(VARCHAR, @column_size) + '), ' + CHAR(13)
			ELSE @column_name + ' ' + @column_type_name +', ' + CHAR(13)
			END
		SET @temp_declare_string = @temp_declare_string + 'DECLARE @' + left(@temp_col_string, len(@temp_col_string) - 3) + CHAR(13)
		SET @temp_table_string = @temp_table_string + @temp_col_string
	END
	ELSE
	BEGIN
		SELECT @cursor_variables = @cursor_variables + ' @' + @column_name	
		SELECT @temp_col_string =
			CASE @column_type_name
				WHEN 'varchar' THEN @column_name + ' ' + @column_type_name + '(' + CONVERT(VARCHAR, @column_size) + ')) ' + CHAR(13)
			ELSE @column_name + ' ' + @column_type_name +') ' + CHAR(13)
			END
		SET @temp_declare_string = @temp_declare_string + 'DECLARE @' + left(@temp_col_string, len(@temp_col_string) - 3) + CHAR(13)
		SET @temp_table_string = @temp_table_string + @temp_col_string
	END
	FETCH NEXT FROM colCur INTO @column_name, @column_num, @column_count, @column_size, @column_type_name
END
IF (@total_rows % (@page_num * @rows_per_page)) > 0
	SET @max_page = (@total_rows / @rows_per_page) + 1
ELSE
	SET @max_page = (@total_rows / @rows_per_page)
DECLARE @total_rows_temp INT
IF (@page_num = @max_page)
	SET @total_rows_temp = (@total_rows - ((@max_page - 1) * @rows_per_page))
ELSE
	SET @total_rows_temp = @total_rows
declare @curStr VARCHAR(4096)
--USE DYNAMIC SQL TO DECLARE VARIABLES AND SCROLL THROUGH CURSOR
SELECT @curStr = @declaration_String + CHAR(13) +
' DECLARE @row_counter int' + CHAR(13) + 
' DECLARE @starting_record int' + CHAR(13) + 
' DECLARE @rows_per_page int' + CHAR(13) + 
' DECLARE @total_rows int' + CHAR(13) +
' DECLARE @rows_on_last_page int' + CHAR(13) +
' SET @total_rows = ' + CONVERT(VARCHAR, @total_rows_temp) + CHAR(13) +
@temp_declare_string + CHAR(13) +
' SET @row_counter = 0' + CHAR(13) + 
' SET @rows_per_page = ' + CONVERT(VARCHAR, @rows_per_page) + CHAR(13) + 
' SET @starting_record = ' + CONVERT(VARCHAR, @starting_record) + CHAR(13) + 
' ' + @temp_table_string + CHAR(13) +
' IF (@total_rows > 0)' + CHAR(13) +
' BEGIN' + CHAR(13) +
' 	FETCH ABSOLUTE @starting_record FROM page_cursor INTO '+ @cursor_variables + CHAR(13) +
' 	INSERT INTO #converge VALUES (' + @cursor_variables + ')' + CHAR(13) +
--'	FETCH NEXT FROM page_cursor INTO '+ @cursor_variables + CHAR(13) +
' 	WHILE (@@FETCH_STATUS= 0) AND (@row_counter < @rows_per_page - 1) AND (@total_rows > 1) AND (@row_counter < @total_rows - 1)'+ CHAR(13) + 
	'BEGIN ' +CHAR(13) +
		--BEGIN ROW
	'	FETCH NEXT FROM page_cursor INTO '+ @cursor_variables + CHAR(13) +
	'	INSERT INTO #converge VALUES (' + @cursor_variables + ')' + CHAR(13) +
	'	SET @row_counter = @row_counter + 1 '+ CHAR(13) +
 	'END' + CHAR(13) +
' END' + CHAR(13) +
'SELECT * FROM #converge' + CHAR(13)
EXEC ( @curStr)
close page_cursor
close colCur
deallocate colCur
deallocate page_cursor
drop table #cols
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO