SQL stored procedure paging code supports billions of volumes of data _mssql

Copy Code code as follows:

CREATE PROCEDURE Page
@tblName varchar (255),--table name
@strGetFields varchar (1000) = ' * ',--columns to be returned
@fldName varchar (255) = ' id ',--sorted field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount bit = 0--Returns the total number of records, not 0 values.
@OrderType bit = 0,--set sort type, not 0 value descending 0:asc 1:desc
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where)
@ID nvarchar = ' ID '--column of the primary table ... 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 means that if @docount passes over 0, the total count is executed. All of the following code is @docount 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) + ' + @strGetFields + ' from ' + @tblName + ' where ' + @strWhere + ' + ' + @strO Rder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + "+ @strGetFields + ' from ' + @tblName + ' +" + @strOrder
--If the first page executes the above code, this will speed up execution
End
ELSE begin
--The following code gives @strsql the SQL code to actually execute
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)