Database Paging Stored Procedures

Use [qiuzhi_db]
GO

SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

--Create a stored procedure
Create PROCEDURE [dbo]. [Pageshow]
(
---This stored procedure cannot sort multiple fields, the sort field must be unique, such as an exception if you sort by reading. Other paging is more efficient.
@tblName varchar (max),--table name
@strGetFields varchar (max) = ' * ',--the column that needs to be returned
@fldName varchar (max) = ',--Sort the field name
Use a field when @joinfldName varchar (max) = ',--is sorted (to prevent errors when querying the second page of multiple tables)
@PageSize int = 10,--page size
@PageIndex int = 1,--page
@doCount bit = 0,--Returns the total number of records, and a value other than 0 returns
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (max) = "--Query criteria (note: Do not add where)
)
As
DECLARE @strSQL varchar (5000)--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 + ' (' + @joinfldName + ') from (select top ' + str ((@PageIndex-1) * @PageSi Ze) + "+ @fldName + ' from ' + @tblName + ' + @strOrder + ') as tbltmp) ' + @strOrder
If @strWhere! = "
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' [email protected]+ ' from '
+ @tblName + ' where ' + @fldName + ' + @strTmp + ' ('
+ @joinfldName + ') from (select top ' + str (@PageIndex-1) * @PageSize) + '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + '
+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End
Print @strSQL
EXEC (@strSQL)
GO

Database Paging Stored Procedures