Copy codeThe Code is as follows:
Create proc commonPagination
@ Columns varchar (500), -- Name of the column to be displayed, separated by commas
@ TableName varchar (100), -- Name of the table to be queried
@ OrderColumnName varchar (100), -- Name of the sorted Column
@ Order varchar (50), -- sort method, ascending to asc, descending to desc
@ Where varchar (100), -- where condition. If no query condition exists, use 1 = 1.
@ PageIndex int, -- index of the current page
@ PageSize int, -- page size (number of records displayed per page)
@ PageCount int output -- total page number, output parameter
As
Begin
Declare @ sqlRecordCount nvarchar (1000) -- Obtain the Statement of the total number of records
Declare @ sqlSelect nvarchar (1000) -- query statement
Set @ sqlRecordCount = n' select @ recordCount = count (*) from'
+ @ TableName + 'where' + @ where
Declare @ recordCount int -- variable that saves the total number of records
Exec sp_executesql @ sqlRecordCount, n' @ recordCount int output', @ recordCount output
-- Dynamic SQL parameter passing
If (@ recordCount % @ pageSize = 0) -- if the total number of records can be divisible by the page size
Set @ pageCount = @ recordCount/@ pageSize -- the total number of pages equals the total number of records divided by the page size
Else -- if the total number of records cannot be divisible by page size
Set @ pageCount = @ recordCount/@ pageSize + 1 -- the total number of pages equals the total number of records divided by the page size plus 1
Set @ sqlSelect =
N 'select' + @ columns + 'from (
Select row_number () over (order'
+ @ OrderColumnName + ''+ @ order
+ ') As tempid, * from'
+ @ TableName + 'where' + @ where
+ ') As tempTableName where tempid'
+ Str (@ pageIndex-1) * @ pageSize + 1)
+ 'And' + str (@ pageIndex * @ pageSize)
Exec (@ sqlSelect) -- execute dynamic SQL
End
Go
-- The following is a call example
Use pubs
Go
Declare @ pageCount int
Exec commonPagination
'Job _ id, job_desc ', 'jobs', 'job _ id ',
'Asc ', '1 = 1', 2,2, @ pageCount output
Select 'total page number: '+ str (@ pageCount)