Create PROCEDURE [dbo]. [Pagination]
@tblName varchar (8000),--table name
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned
@fldName varchar (255) = ',--Sort the field name
@PageSize int,--page size
@PageIndex int,--page
@doCount bit,--returns the total number of records, and a value other than 0 returns
@OrderType bit,--set sort type, not 0 value descending
@strWhere varchar (1500) = "--Query criteria (note: Do not add where)
As
DECLARE @strSQL varchar (8000)--the subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type
If @doCount! = 0
Begin
If @strWhere! = "
Set @strSQL = ' SELECT count (*) as total from ' + @tblName + ' where ' [email protected]
Else
Set @strSQL = ' SELECT count (*) as total from ' + @tblName + '
End
--The above code means that if @docount passes over 0, the total number of executions is counted. All of the following codes are in the case of @docount to 0
Else
Begin
If @OrderType! = 0
Begin
Set @strTmp = ' < (select Min ')
Set @strOrder = ' ORDER BY ' + @fldName + ' desc '
If @ordertype is not 0, it is important to perform descending order.
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) + ' [email protected]+ ' from ' + @tblName + ' where ' + @strWhere + ' + @s Trorder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from ' + @tblName + ' + @strOrder
--If the above code is executed on the first page, this will speed up execution
End
Else
Begin
--The following code gives the @strsql to actually execute the SQL code
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from '
+ @tblName + ' where ' + @fldName + ' + @strTmp + ' (' + @fldName + ') from (select top ' + str ((@PageIndex-1) * @PageSize) + "+ @fldName + ' from ' + @tblName + ' + @strOrder + ') as tbltmp) ' + @strOrder
If @strWhere! = "
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from '
+ @tblName + ' where ' + @fldName + ' + @strTmp + ' ('
+ @fldName + ') from (select top ' + str (@PageIndex-1) * @PageSize) + '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + '
+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End
EXEC (@strSQL)
Simplified version of SQL Server paging