Publish a general data paging Stored Procedure

Create procedure sp_page
@ TB varchar (50), -- table name
@ Col varchar (50), -- pagination by this column
@ Coltype int, -- @ Col column type, 0-number type, 1-character type, 2-Date and Time Type
@ Orderby bit, -- sort, 0-order, 1-inverted
@ Collist varchar (800), -- List of fields to be queried, * indicates all fields
@ Pagesize int, -- number of records per page
@ Page int, -- specify the page
@ Condition varchar (800), -- Query Condition
@ Pages int output -- total number of pages
As
/*
Function Description: queries records that meet the specified conditions in a specified table by Page Based on the specified column. The page can be sorted in reverse order.
Query can specify the page size, specify any page to query, specify the list of output fields, and return the total number of pages
Author: pbsql
Version 1.10
Last modified: 2004-11-29
*/
Declare @ SQL nvarchar (4000), @ where1 varchar (800), @ where2 varchar (800)
If @ condition is null or rtrim (@ condition) =''
Begin -- no query Conditions
Set @ where1 = 'where'
Set @ where2 =''
End
Else
Begin -- with query Conditions
Set @ where1 = 'where ('+ @ condition +') and '-- this condition is added if conditions exist.
Set @ where2 = 'where ('+ @ condition +') '-- this condition is added if no conditions exist.
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 -- calculate the total number of pages
If @ orderby = 0
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
'From' + @ TB + @ where1 + @ Col + '> (select max (' + @ Col + ')' +
'From (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 (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

For details, see:
Http://blog.csdn.net/pbsql/archive/2004/11/30/199657.aspx

Http://community.csdn.net/Expert/topic/3587/3587201.xml? Temp =. 4292414.