SQL Server General paging stored procedures, with stored procedures can improve efficiency and save time.
SQL Server General paging stored procedures, with stored procedures can improve efficiency and save time.
The 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)
