Create Procedure SP_Page
@TB varchar (50), - Table name
@col varchar (50), - Page by this column
@ColType int, - @ COL column, 0- Digital Type, 1-Character Type, 2-Date Time Type
@ORDERBY BIT, - Sort, 0 - Order, 1 - Reverse
@collist varchar (800), - to query the list of fields, * Represents all fields
@PageSize Int, - number per page
@Page Int, - Specify page
@condition varchar (800), - Query Conditions
@Pages Int Output - Total number
AS
/ *
Function Description: The records that meet the conditions in the specified table are paid in paging in the specified column, and the paging can be sequentially, reverse
The query can specify the page size, specify the query of any page, specify the list of output fields, return to 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 - there is a query condition
Set @ where1 = 'where (' @ condition ') and' - there is a condition plus this condition
Set @ where2 = 'where (' @ condition ')' - This condition is added without conditions
End
Set @ SQL = 'SELECT @ Pages = CEILING ((*) 0.0) /' Cast (@PageSize As Varchar)
') From' @ TB @ WHERE2
EXEC SP_EXECUTESQL @ SQL, N '@ Pages Int Output', @ Pages Output - Calculate Total Pages
IF @ otby = 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 'dec) 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 TEN '' ELSE 'DESC' END
EXEC (@SQL)
Go
