Suppose you have a stored procedure that does wonderful things, but returns a lot of data. So you want to page that data but want to make the paging happen as far back in the data retrieval cycle as possible. So if your data is coming from a SQL Server database, you can apply the pattern described below:
In order to make the paging minimally invasive to already working stored procedures, create a new stored procedure which will return the paged record set and the total number of records. Here it us:
create proc usp_SkipTake(
@columnDefs varchar(1000), --the column names and dataTypes in the exact same order as the stored procedure which will be paged.
--example: firstName varchar(50), lastname varchar(50)
@sprocWithInputParams varchar(1000), --this is the stored procedure which will be paged.
@skip int, --the number of records to skip
@take int, --the number of records to return
@orderBy varchar(255)=null --allows you to specify the sort order of the data. This is the only new feature your old stored procedure will need to implement to fit this design pattern.
)
as Begin
declare @p varchar(8000)
declare @lastRecord varchar(9)
set @lastRecord = isnull(@skip,0) + case when isnull(@take,0)=0 then 1000000 else @take end
set @p = 'create table #tmptbl (' + @columnDefs + ', _rowNo int identity)' +
'insert into #tmptbl exec ' + @sprocWithInputParams + '
select * from #tmptbl
where (_rowNo > ' + convert(varchar(9), @skip) + ' AND _rowNo <= ' + @lastRecord + ')
select recordCount=count (_rowNo) from #tmptbl
drop table #tmptbl'
exec(@p)
End
Note that if you supply the @take value of 0, it will default to getting the first million records. This was an arbitrary number. I assumed that would be the same as retrieving all records for the vast majority of use cases.
A note about performance: I found that this approach will likely double the amount of time it takes to return data from the database. Coming up next, we will examine how caching can help improve performance.
