Create procedure [DBO]. [up_pager]
@ Table varchar (2000), -- table name
@ Col varchar (50), -- pagination by this column
@ 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
As
Declare @ SQL nvarchar (4000), @ where1 varchar (800), @ where2 varchar (800 ),
@ Total_item int, @ total_page int
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 @ total_item = ceiling (count (*) + 0.0) '+') from '+ @ table + @ where2
Exec sp_executesql @ SQL, n' @ total_item int output', @ total_item output -- calculate the total number of items
Set @ total_page = ceiling (@ total_item + 0.0)/@ pagesize) -- calculate the total number of pages
If @ orderby = 0
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
',' + Cast (@ total_item as varchar) + 'As total_item' +
',' + Cast (@ total_page as varchar) + 'As total_page' +
'From mailto: '+ @ table + @ where1 + @ Col +' % 3E (select max ('+ @ Col +') '+
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ table + @ where2 + 'ORDER BY' + @ Col + ') T) Order by' + @ col
Else
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
',' + Cast (@ total_item as varchar) + 'As total_item' +
',' + Cast (@ total_page as varchar) + 'As total_page' +
'From mailto: '+ @ table + @ where1 + @ Col +' % 3C (select Min ('+ @ Col +') '+
'From (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ table + @ where2 + 'ORDER BY' + @ Col + 'desc) T) Order by' +
@ Col + 'desc'
If @ page = 1 -- first page
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist +
',' + Cast (@ total_item as varchar) + 'As total_item' +
',' + Cast (@ total_page as varchar) + 'As total_page' +
'From' + @ table +
@ Where2 + 'ORDER BY' + @ Col + case @ orderby when 0 then' 'else' DESC 'end
-- Print @ SQL
Exec (@ SQL)
Test in SQL (how to use it)
Exec up_pager '(select * from Table Name) A', 'column name to be sorted', 0-order or 1-inverted, 'show column', number of records per page, specify the page, 'condition'
Exec up_pager '(select * From t_gather_page) A', 'savetime', 1 ,''