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