Commonly used paging SQL

1.
Select Top PageSize * From Table where biaoshi not in (Select Top (PageIndex-1) * PageSize biaoshi From Table)

2.
Create procedure [dbo]. [UP_Common_Pager] (
@ TblName nvarchar (255), -- table name
@ Fields nvarchar (1000) = '*', -- the column to be returned
@ OrderField nvarchar (255) = '', -- Name of the sorted Field
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ DoCount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ OrderType bit = 1, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere nvarchar (1500) = '', -- Query condition (Note: Do not add where)
@ SQL nvarchar (4000) = ''out
)
Begin
If @ pageIndex = 0
Set @ pageIndex = 1
Declare @ strSQL nvarchar (4000) -- subject sentence
Declare @ strTmp nvarchar (110) -- Temporary Variable
Declare @ strOrder nvarchar (400) -- sort type
If @ doCount! = 0
Begin
If @ strWhere! =''
Set @ strSQL = 'select count (*) as Total from '+ @ tblName + 'where' + @ strWhere
Else
Set @ strSQL = 'select count (*) as Total from '+ @ tblName +''
End
-- The above Code indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ doCount
Else
Begin
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'ORDER BY' + @ OrderField + 'desc'
-- If @ OrderType is not 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'ORDER BY' + @ OrderField + 'asc'
End
If @ PageIndex = 1
Begin
If @ strWhere! =''
Set @ strSQL = 'select top' + str (@ PageSize) + ''+ @ Fields + 'from' + @ tblName + 'where' + @ strWhere +'' + @ strOrder
Else
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ Fields + 'from' + @ tblName +'' + @ strOrder
End
Else
Begin
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ Fields + 'from' + @ tblName + 'where ['+ @ OrderField +'] '+ @ strTmp +' (['+ @ OrderField +']) from (select top '+ str (@ PageIndex-1) * @ PageSize) +' ['+ @ OrderField +'] from '+ @ tblName + ''+ @ strOrder + ') as tblTmp) '+ @ strOrder
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ Fields + 'from' + @ tblName + 'where ['+ @ OrderField +'] '+ @ strTmp +' (['+ @ OrderField +']) from (select top '+ str (@ PageIndex-1) * @ PageSize) + '[' + @ OrderField + '] from' + @ tblName + 'where' + @ strWhere + ''+ @ strOrder +') as tblTmp) and '+ @ strWhere + ''+ @ strOrder
End
End
Set @ SQL = @ strSql
Exec (@ strSQL)
End