Paging Stored Procedure Code

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.