For more information about SQL Server paging query stored procedures, see.
For SQL Server paging query stored procedures, refer.
The Code is as follows:
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)
The Code is as follows:
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 ,''
