Ok, here is another attempt, see what you think. Sean & I were discussing this today, other ideas we had were to use the paging ability of ADO, use a keyset cursor which would basically do what Im doing here, hold the keys in a temp table. Anyone have thoughts on those?
If you didnt drop the temp table each time (I did, just to be clean) you could leverage the work you did on the first query on subsequent 'pages'. We've implemented an internal process that does that and have been very pleased with the results.
Andy
create proc usp_GetSubset @NameOfPKeyColumn varchar(50), @Select varchar(500), @From varchar(500), @BatchSize int = 10, @BatchesToSkip as int = 0 as
--9/17/01 Andy Warren
--Second try at offering an alternative to a cursor based solution, this is designed
--to be more generic, if somewhat clunky to use.
--NameofPkeyColumnObviously, your primarykey on the table you're selecting from
--SelectColumns you want to return, you MUST include the primarykey you
--referenced in NameofPkeyColumn
--FromThe rest of your select including your where and order by
--BatchSizeNumber of rows to return
--BatchesToSkipNumber of 'pages' to skip
declare @StartRow int
declare @EndRow int
declare @Sql varchar(1000)
--get all the rows into a temp table in the order we plan to use them, could add an
--index here if you had a LOT of rows
Create Table ##Temp (SortOrderID int identity(1,1), PKey sql_variant)
set @SQL = 'insert into ##temp (Pkey) select ' + @NameOfPKeyColumn + ' ' + @From
exec(@SQL)
--find the starting point for the 'page' we want, remembering that we'll be doing
--a 'between'
set @StartRow = (@BatchSize * @BatchesToSkip) + 1
--then the end point
set @EndRow = (@StartRow + @BatchSize) - 1
--get the data
set @sql='Select * from (' + @Select + ' ' + @From + ') O inner join ##Temp T on o.' + @NameOfPkeyColumn + ' = t.pkey where t.SortOrderID between ' + convert(varchar(10),@StartRow) + ' and ' + convert(varchar(10), @EndRow) + ' '
exec(@sql)
--clean up
drop table ##temp