Copy Code code as follows:
Create proc Commonpagination
@columns varchar (500),--column names to display, separated by commas
@tableName varchar (100)--Name of the table to query
@orderColumnName varchar (100),--sorted column names
@order varchar (50),--Sort the way, ascending to ASC, descending to DESC
@where varchar (m),--where conditions, if not with the query conditions, please use 1=1
@pageIndex int,--current page index
@pageSize int,-page size (number of record bars per page)
@pageCount int output-Total pages, output parameters
As
Begin
declare @sqlRecordCount nvarchar (1000)--a statement that gets the total number of record bars
declare @sqlSelect nvarchar (1000)--query statement
Set @sqlRecordCount =n ' Select @recordCount =count (*) from '
+ @tableName + ' where ' + @where
DECLARE @recordCount INT--A variable that holds the total number of record bars
EXEC sp_executesql @sqlRecordCount, N ' @recordCount int output ', @recordCount output
--Dynamic SQL parameters
if (@recordCount% @pageSize = 0)--if the total number of records can be divisible by the page size
Set @pageCount = @recordCount/@pageSize--Total number of pages equals total record bar divided by page size
Else--If the total number of records cannot be divisible by the page size
Set @pageCount = @recordCount/@pageSize + 1--Total number of pages equals total record bar divided by page size plus 1
Set @sqlSelect =
N ' SELECT ' + @columns + ' from (
Select Row_number () over
+ @orderColumnName + ' + @order
+ ') as tempid,* from '
+ @tableName + ' where ' + @where
+ ') as Temptablename where tempid between '
+str ((@pageIndex-1) * @pageSize + 1)
+ ' and ' +str (@pageIndex * @pageSize)
EXEC (@sqlSelect)--Execute dynamic SQL
End
Go
--Here is the 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 pages: ' + str (@pageCount)
