A stored procedure that pages data

Stored Procedures | pagination | data
CREATE PROCEDURE Sp_page
@tb varchar (50),--table name
@col varchar (50),--Paging by this column
@coltype int,--type of @col column, 0-digit type, 1-character type, 2-date Time type
@orderby bit,--sort, 0-order, 1-Descending
@collist varchar,--The list of fields to query, * represents all fields
@pagesize int,--Number of records per page
@page int,--Specify page
@condition varchar,--query criteria
@pages int OUTPUT-Total pages
As
/*
Function Description: The records that meet the criteria in the specified table are paged out by the specified column, and the paging can be ordered, reversed
Query can specify page size, specify query for any page, specify Output field list, return total pages
Author: pbsql
Version: 1.10
Last modified: 2004-11-29
*/
DECLARE @sql nvarchar (4000), @where1 varchar (a), @where2 varchar (800)
IF @condition is null or RTRIM (@condition) = '
begin--No query criteria
SET @where1 = ' WHERE '
SET @where2 = '
End
ELSE
begin--have query conditions
SET @where1 = ' WHERE (' + @condition + ') and '--the condition is added to this condition
SET @where2 = ' WHERE (' + @condition + ') '--there is no condition to add this condition
End
SET @sql = ' SELECT @pages =ceiling ((COUNT (*) +0.0)/' +cast (@pagesize as varchar) +
') from ' + @tb + @where2
EXEC sp_executesql @sql, N ' @pages int OUTPUT ', @pages output--total page Count
IF @orderby =0
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + ' + @collist +
' From (select > ' + @tb + @where1 + @col + ' > (select MAX (' + @col + ') ' +
' From (the SELECT top ' +cast (@pagesize * (@page-1) as varchar) + ' +
@col + "from" + @tb + @where2 + ' ORDER BY ' + @col + ') T "ORDER by" + @col
ELSE
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + ' + @collist +
' From ' + @tb + @where1 + @col + ' < (SELECT MIN (' + @col + ') ' +
' From (the SELECT top ' +cast (@pagesize * (@page-1) as varchar) + ' +
@col + "from" + @tb + @where2 + ' ORDER BY ' + @col + ' DESC) T "ORDER by ' +
@col + ' DESC '
IF @page =1--First page
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + "+ @collist + ' from ' + @tb +
@where2 + ' ORDER BY ' + @col +case @orderby when 0 THEN ' ELSE ' DESC ' end
EXEC (@sql)
Go

This stored procedure is efficient, has used 5 million data test (indexed), only return paging only 3 seconds, affecting the efficiency of the place is to calculate the total number of pages, if you do not need to be able to comment out

--Test sample
DECLARE @pages int
Select Identity (int,1,1) id,getdate () dt,xx=cast (' as varchar ') into #t
From sysobjects
Update #t set Dt=dateadd (DAY,ID-200,DT),
xx= ' xxxx ' +right (' 000000 ' +cast (id as varchar (10)), 6

exec sp_page ' #t ', ' id ', 0,0, ' * ', 10,2, ', @pages output--take the second page in ID order
exec sp_page ' #t ', ' id ', 0,1, ' * ', 10,2, ', @pages output--take the second page by ID reverse
exec sp_page ' #t ', ' xx ', 1,0, ' * ', 10,3, ', @pages output--take the third page in XX order
exec sp_page ' #t ', ' xx ', 1,1, ' * ', 10,3, ', @pages output--take the third page in reverse order XX
exec sp_page ' #t ', ' DT ', 2,0, ' * ', 10,2, ', @pages output--take the second page in DT order
exec sp_page ' #t ', ' DT ', 2,1, ' * ', 10,2, ', @pages output--Press DT to reverse the second page

Select total number of pages = @pages

drop table #t