Alter procedure [DBO]. [Page]
@ TB varchar (20), -- table name
@ Col varchar (100), -- pagination by this column
@ Orderby bit, -- sort, 0-order, 1-inverted
@ Collist varchar (1500), -- List of fields to be queried, * indicates all fields
@ Pagesize int, -- number of records per page
@ Page Int = 1, -- specify the page
@ Condition varchar (1500) = '', -- 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
Original Author: pbsql
Version:. 11
Last modified:-5-31
*/
Declare @ SQL nvarchar (4000), @ where1 varchar (1500), @ where2 varchar (1500)
If @ condition is null or rtrim (@ condition) =''
Begin -- no query Conditions
Set @ where1 = 'where'
Set @ where2 =''
End
Else
Begin -- with query Conditions
Set @ where1 = @ condition + 'and' -- this condition is added when conditions exist.
Set @ where2 = @ condition + ''-- this condition is added because no conditions exist.
End
Set @ SQL = 'select @ pages = (count (*) '+
') From' + @ TB + @ where2
-- Print @ SQL
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) '+ @ TB +') 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
-- Print @ SQL
Exec (@ SQL)