Paging through a resultset seems to be a common problem with Winforms and Webforms. While you could do it all clientside with loading your complete resultset into a Dataset and then page through it, this will only be reasonable with a small amount of data. If you try to do this with resultsets of more than 1000 rows, the dataset will soon be consuming all of your memory or bandwith when transfering the data from the SQL Server to the Dataset on the client. The most convenient way of handling large chunks of data would be to page through them on the server side. But there seemed to be no good reusable solution for MS SQLServer 2000. I’ve seen solutions with ‘Yukon’ and the ROWNUMBER()-function or solutions that saves the resultset for each user in a separate table and then looping through it but I haven’t seen any view-like solution to this.
Here’s mine: I created a procedure that takes the displayed fields, the table(s), the filter (or where clause), the grouping, the sorting clause, the page size (rows per page) and the requested page as parameters.
The procedure then creates a a sub-select that returns all rows from the first row to the [pagesize] * [requested page] row. Then the procedure creates a select that returns the bottom n rows (page size) by flipping the sort order and returning the TOP n rows of the subselect. In the last step the Resultset is sorted once more by the initial sorting clause to get the rows in the requested order.
You must provide at least one sorting criteria to make it work! After that you can run this procedure on any database you have. You can also join tables simply by adding the join statement into the table parameter, but DO NOT use the table identifier with the fields parameter (like ‘Suppliers.CompanyName’). It’s better to always use a defined View over your paged data..
Hope you like it:
– ************************************************************************
– delete any existing procedure
– ************************************************************************
if exists (select 1 from sysobjects where name = ’sp_genericPaging’ and xtype = ‘P’)
begin
drop procedure sp_genericPaging
end
GO
create procedure sp_genericPaging
@fields nvarchar(4000),
@table nvarchar(4000),
@filter nvarchar(4000) = ”,
@grouping nvarchar(4000) = ”,
@sort nvarchar(4000) = ”,
@pageSize int = 100,
@page int = 1
as
set nocount on
declare @statement nvarchar(4000)
– ****************************************************************************
– prepare a subselect for the last matches
– ****************************************************************************
set @statement =
‘(SELECT TOP ‘ + cast((@pageSize * (@page)) as varchar(100)) + ‘ ‘ + @fields +
‘ FROM ‘ + @table +
case when isnull(@filter,”) != ” then ‘ WHERE ‘ + @filter else ” end +
case when isnull(@grouping,”) != ” then ‘ GROUP BY ‘ + @filter else ” end +
case when isnull(@sort, ”) != ” then ‘ ORDER BY ‘ + @sort else ” end + ‘) SUBSEL’
– ****************************************************************************
– flip the sort clause
– ****************************************************************************
declare @pos int, @idx int, @len int, @substr nvarchar(250), @retsort nvarchar(400)
set @retsort = ”
set @pos = 1
set @idx = 1
if len(@sort) > 0
begin
while @idx > 0
begin
– find commas
set @idx = charindex(’,', @sort, @pos)
–check length
if @idx = 0
set @len = len(@sort) – @pos + 1
else
set @len = @idx – @pos
– cut out criteria
set @substr = substring(@sort, @pos, @len)
– reset positioning
set @pos = @pos + @len + 1
– change sorting
if charindex(’desc’, @substr) > 0
begin
set @substr = replace(@substr, ‘desc’, ‘asc’)
end
else if charindex(’asc’, @substr) > 0
begin
set @substr = replace(@substr, ‘asc’, ‘desc’)
end
else
begin
set @substr = @substr + ‘ desc’
end
– recreate flipped sorting
set @retsort = @retsort + case when len(@retsort) > 0 then ‘, ‘ else ” end + @substr
end
end
else
begin
set @retsort = ”
end
– ***************************************************************************
– add the paging select
– ***************************************************************************
set @statement =
‘(SELECT TOP ‘ + cast(@pageSize as varchar(100)) + ‘ ‘ + @fields +
‘ FROM ‘ + @statement +
case when isnull(@retsort, ”) != ” then ‘ ORDER BY ‘ + @retsort else ” end + ‘) PAGESEL’
– ***************************************************************************
– flip the sorting once more to get the correct sorting
– ***************************************************************************
set @statement =
‘SELECT ‘ + @fields +
‘ FROM ‘ + @statement +
case when isnull(@sort, ”) != ” then ‘ ORDER BY ‘ + @sort else ” end + ”
– ****************************************************************************
– execute the selection and return the resultset
– ****************************************************************************
exec(@statement)
GO
/*testselect*/
exec sp_genericPaging
‘ProductID, ProductName, CompanyName, UnitPrice’, –feldnamen
‘Products join Suppliers on Products.SupplierID = Suppliers.SupplierID’, –tabelle(n)
”, –filter (WHERE)
”, –grouping
‘ProductID’, –sort
20, –pagesize
3 –currentpage