Copy codeThe Code is as follows:
/*
* @ Curentpage current page
* @ Pagesize number of records per page
* @ TableName: Table Name
* @ Key primary key (Auto sort)
* @ Where query Condition
1) null is null
2) do not include where when there are query Conditions
* @ Order '0' indicates that desc '1' is asc
* @ PageCount total page number
*/
Create procedure Page
@ Currentpage int, @ pagesize int,
@ TableName varchar (30), @ key varchar (30 ),
@ Where varchar (50), @ order varchar (1 ),
@ PageCount int, @ str varchar (450) output
As
Begin
--------------- Executed SQL statement ------------
Declare @ SQL nvarchar (400), @ ordreby nvarchar (200)
Declare @ tempsql1 varchar (200), @ tempsql2 varchar (200)
--------------- Total number of records -----------------
Declare @ count int
--------------- Temporary Variable ------------------------
Declare @ temp1 int, @ temp2 int
Set @ TableName = ''+ @ TableName +''
Set @ key = ''+ @ key +''
If @ order = '0'
Set @ ordreby = 'ORDER BY' + @ key + 'desc'
Else
Set @ ordreby = 'ORDER BY' + @ key
If @ where = 'null'
Set @ SQL = 'select @ count = count (*) from '+ @ TableName
Else
Set @ SQL = 'select @ count = count (*) from '+ @ TableName + 'where' + @ where
------------ @ Count to pay the value (declare the variable @ count to indicate the output type )---------------------------
Exec sp_executesql @ SQL, n' @ count int out', @ count out
------------ Calculate the total number of pages ------------------------------
If (@ count % @ pagesize) = 0
Set @ pagecount = @ count/@ pagesize
Else
Set @ pagecount = @ count/@ pagesize + 1
----------- Determine whether the current page is displayed abnormal ------------------
If @ currentpage> @ pagecount
Set @ currentpage = @ pagecount
If @ currentpage <1
Set @ currentpage = 1
---------- The number of records is less than the number of records displayed on the page -----------------
If (@ currentpage = 1)
Begin
If @ where = 'null'
Set @ where =''
Else
Set @ where = 'where' + @ where
Set @ SQL = 'select top '+ str (@ pagesize) +' * from' + @ TableName + @ where + @ ordreby
End
Else
Begin
/** // * --------------- Desc ----------------------
* @ Temp1 indicates the previous record
* @ Temp2 indicates the subsequent record
* Assume a total of 77 records, with 10 records each time. Retrieve 67 ~ 58 (page 2nd), remove the previous 57 (1 ~ 57) and 10 (77 ~ 66)
*/
If @ order = 0
Begin
Set @ temp1 = @ count-@ currentpage * @ pagesize
If @ temp1 <0
Set @ temp1 = 0
Set @ temp2 = (@ currentpage-1) * @ pagesize
If @ where = 'null'
Begin
Set @ tempsql1 = 'select top '+ str (@ temp1) + ''+ @ key + 'from' + @ TableName + 'ORDER BY' + @ key
Set @ tempsql2 = 'select top '+ str (@ temp2) + ''+ @ key + 'from' + @ TableName + @ ordreby
End
Else
Begin
Set @ tempsql1 = 'select top '+ str (@ temp1) + ''+ @ key + 'from' + @ TableName + 'where' + @ where + 'ORDER BY' + @ key
Set @ tempsql2 = 'select top '+ str (@ temp2) + ''+ @ key + 'from' + @ TableName + 'where' + @ where + @ ordreby
End
Set @ SQL = 'select top '+ str (@ pagesize) +' * from' + @ TableName + 'where' + @ key + 'not in'
Set @ SQL = @ SQL + '(' + @ tempsql1 + ') and'
Set @ SQL = @ SQL + @ key + 'not in ('+ @ tempsql2 + ')'
If @ where = 'null'
Set @ SQL = @ SQL + @ ordreby
Else
Set @ SQL = @ SQL + 'and' + @ where + @ ordreby
End
/** // * ---------------- Asc ---------------------
* @ Temp indicates the total number of records shown above
* Remove @ temp and retrieve pagesize.
*/
Else
Begin
Set @ temp1 = (@ currentpage-1) * @ pagesize
If @ where = 'null'
Set @ tempsql1 = 'select top '+ str (@ temp1) + ''+ @ key + 'from' + @ TableName + @ ordreby
Else
Set @ tempsql1 = 'select top '+ str (@ temp1) + ''+ @ key + 'from' + @ TableName + 'where' + @ where + @ ordreby
Set @ SQL = 'select top '+ str (@ pagesize) +' * from' + @ TableName + 'where' + @ key + 'not in'
Set @ SQL = @ SQL + '(' + @ tempsql1 + ')'
If @ where = 'null'
Set @ SQL = @ SQL + @ ordreby
Else
Set @ SQL = @ SQL + 'and' + @ where + @ ordreby
End
/**//*-------------------------------------*/
End
Set @ str = @ SQL
-- Exec sp_executesql @ SQL
End
GO