SQL Server General paging Stored Procedure

SQL Server General paging stored procedures, with stored procedures can improve efficiency and save time.

 If   Exists ( Select * From Sysobjects Where Name = 'Commonpagination' ) Drop   Proc Commonpagination Go  Create   Proc [DBO]. [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), -- Sort the column name @ Order   Varchar (50), -- sorting method. The Ascending Order is ASC, And the descending order is Desc @ Where   Varchar (100 ),-- Where Condition. If no query condition is specified, use 1 = 1 @ pageindex. Int , -- Current page index @ pagesize Int , -- Page size (number of records displayed per page) @ pagecount Int   Output -- Total number of pages, output parameters 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 Passing Parameters 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  

 

-- The following is a call example
UsePubs
Go
Declare@ PagecountInt
ExecCommonpagination
'Job _ id, job_desc','Job','Job _ id',
'Asc','1 = 1', 2, 2, @ pagecountOutput
Select 'The total number of pages is :'+ STR (@ pagecount)

 

 If   Exists ( Select * From Sysobjects Where Name = 'Pagination' ) Drop  Procedure Pagination Go  Create   Procedure Pagination @ Columns Varchar (500 ), -- The columns to be displayed, divide by comma @ Tablename Varchar (100 ), -- The name of the table to be searched @ Ordercolumnname Varchar (100 ), -- The name of the column to be used in order @ Order   Varchar (50 ), -- The order method, ASC or DESC @Where   Varchar (100 ), -- The where condition, if there is not conditon use 1 = 1 @ Pageindex Int , -- Current page index @ Pagesize Int , -- The size of the page @ Pagecount Int   Output   -- The total page count, define as output parameter  As  Begin  Declare @ Sqlrecordcount nvarchar (100) -- The SQL statement to get the total count of the records Declare @ Sqlselect nvarchar (1000) -- The SQL select statment  Set @ Sqlrecordcount = N 'Select @ recordcount = count (*) from' + @ Tablename + 'Where' + @ Where  Declare @ Recordcount Int  Exec Sp_executesql @ sqlrecordcount, n '@ Recordcount int output' , @ Recordcount Output   -- Transfer the parameter dynamic  If (@ Recordcount % @ pagesize = 0)Set @ Pagecount = @ recordcount/@ pagesize Else  Set @ Pagecount = @ recordcount/@ pagesize + 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) End  Go