Copy codeThe Code is as follows:
Create procedure page
@ TblName varchar (255), -- table name
@ StrGetFields varchar (1000) = '*', -- the column to be returned
@ FldName varchar (255) = 'id', -- 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 = 0, -- set the sorting type. If the value is not 0, the order is 0: asc 1: desc.
@ StrWhere varchar (1500) = '', -- Query condition (Note: Do not add where)
@ ID nvarchar (50) = 'id' -- main table column .. Preferably the primary key
AS
Declare @ strSQL varchar (5000) -- subject sentence
Declare @ strTmp varchar (110) -- Temporary Variable
Declare @ strOrder varchar (400) -- Sort class
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' + @ fldName + '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' + @ fldName + 'asc'
End
If @ PageIndex = 1 begin
If @ strWhere! =''
Set @ strSQL = 'select top' + str (@ PageSize) + ''+ @ strGetFields + 'from' + @ tblName + 'where' + @ strWhere +'' + @ strOrder
Else
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from' + @ tblName +'' + @ strOrder
-- Execute the above Code on the first page, which will speed up the execution.
End
Else begin
-- The following code gives @ strSQL the SQL code to be actually executed
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from'
+ @ TblName + 'where' + @ fldName + ''+ @ strTmp + '(' + @ ID + ') from (select top' + str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName + @ strOrder + ') as tblTmp)' + @ strOrder
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from'
+ @ TblName + 'where' + @ fldName + ''+ @ strTmp + '('
+ @ ID + ') from (select top' + str (@ PageIndex-1) * @ PageSize) +''
+ @ FldName + 'from' + @ tblName + 'where' + @ strWhere +''
+ @ StrOrder + ') as tblTmp) and' + @ strWhere + ''+ @ strOrder
End
End
Exec (@ strSQL)