Paging Stored Procedure for sorting any field

A recent project, many of which require the paging Function
In addition, there is a common feature in the paging areas, that is, paging, but not sorting by ID (Primary Key), but sorting by other columns with repeated values, for example, if there is a page that lists nearly 10 thousand customer records and needs to be sorted by the customer's paid amount, in this case, if you use the common paging stored procedure on the Internet, for example, although the paging stored procedure below is great, it is not feasible to use it here: (this stored procedure was written by a senior developer on CSDN)

Create procedure GetRecordFromPage
@ TblName varchar (255), -- table name
@ FldName varchar (255), -- field name
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ IsCount 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 sorting type is descending.
@ StrWhere varchar (4000) = ''-- Query condition (Note: Do not add where)
AS

Declare @ strSQL varchar (6000) -- subject sentence
Declare @ strTmp varchar (6000) -- Temporary Variable
Declare @ strOrder varchar (6000) -- sort type

If @ OrderType! = 0
Begin
Set @ strTmp = "<(select min"
Set @ strOrder = "order by [" + @ fldName + "] desc"
End
Else
Begin
Set @ strTmp = "> (select max"
Set @ strOrder = "order by [" + @ fldName + "] asc"
End

Set @ strSQL = "select top" + str (@ PageSize) + "* 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) + "* from ["
+ @ TblName + "] where [" + @ fldName + "]" + @ strTmp + "(["
+ @ FldName + "]) from (select top" + str (@ PageIndex-1) * @ PageSize) + "["
+ @ FldName + "] from [" + @ tblName + "] where (" + @ strWhere + ")"
+ @ StrOrder + ") as tblTmp) and (" + @ strWhere + ")" + @ strOrder

If @ PageIndex = 1
Begin
Set @ strTmp = ""
If @ strWhere! =''
Set @ strTmp = "where (" + @ strWhere + ")"

Set @ strSQL = "select top" + str (@ PageSize) + "* from ["
+ @ TblName + "]" + @ strTmp + "" + @ strOrder
End

If @ IsCount! = 0
Set @ strSQL = "select count (*) as Total from [" + @ tblName + "]"

Exec (@ strSQL)
I have been using the GO paging stored procedure in some places. If you have used it, I believe it should recognize its performance. However, from the code, we can see that it has a basic requirement, that is, a table or view must have a primary key. The implicit requirement is that the sorting field must be a field with a unique value, which is impossible for the application I mentioned above, although this stored procedure is useful, it cannot be used in this scenario.
Later, I saw another guy in CNBLOGS talking about ASP. net forum uses the temporary table paging method to write a paging stored procedure for a temporary table, which is also common and easier to implement, as shown below:
Create procedure dbo. GetPageRecord
(
@ TblName varchar (255), -- table name
@ Fldlist varchar (1000) = '*',
@ Fldname varchar (255), -- Sort Field
@ Keyfield varchar (255), -- primary key
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Iscount 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 varchar (4000) = ''-- Query condition (Note: Do not add where)
)
As
Set nocount on
Declare @ pagelowerbound int
Declare @ pageupperbound int

-- Set the page Bounds
Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1

-- Create a temp table to store the select results
Create Table # temp
(
Recno int identity (1, 1) not null,
Oldid int
)
-- Generate record
Declare @ sqlstr varchar (6000)
Set @ sqlstr = 'select' + @ keyfield + 'from' + @ tblname
If (@ strwhere <> '')
Begin
Set @ sqlstr = @ sqlstr + 'where ('+ @ strwhere + ')'
End
Set @ sqlstr = @ sqlstr + 'ORDER BY' + @ fldname
If (@ ordertype = 0)
Begin
Set @ sqlstr = @ sqlstr + 'asc'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'desc'
End
Set @ sqlstr = 'insert into # temp (oldid) '+ @ sqlstr
Execute (@ sqlstr)
Set @ sqlstr = 'select' + @ fldlist + 'from' + @ tblname + 'tablea (nolock), # temp t where T. oldid = tablea. '+ @ keyfield +' and T. recno> '+ Cast (@ pagelowerbound as varchar) +' and T. recno <'+ Cast (@ pageupperbound as varchar) + 'order by T. recno'
Execute (@ sqlstr)
After using the stored procedure of this temporary table solution, GO has spent nearly four days testing the performance to improve the performance. Later, I found that no matter what method, due to the performance constraints of the temporary table, the SELECT statement can be optimized. However, it also takes too much time to create and discard temporary tables on the read/write disk, which leads to poor stability of the stored procedure. why. I did a test. A person can access the WEB page paginated using this method, and the speed is also satisfactory (2000 records). However, when more than five people access this page, IIS will cause the ASP working process to fail to communicate with the W3P process due to heavy load, and the SQL SERVER will also encounter a buffer error from time to time. why? I think it is because a temporary table is used. Later, I saw someone proposing to use table variables. However, if you think about it, it still won't work, because my query condition is a dynamic input parameter.
Due to poor performance, my WEB application frequently becomes a machine when processing pages, so I am determined to rewrite the new stored procedure.
I was writing data from to 12.1 last night. In fact, the principle of paging Stored Procedure writing should be quite simple. The following figure shows the different ideas of the two stored procedures.

The principle is very simple and everyone can get it. The problem is how the transitional result set is generated. If it is not because of the table name, condition, and page number, we can use a select top statement to complete it. However, the problem is: No, because they are parameterized, the following key content will remove your concerns in this regard, let's take a look at the hidden secrets in ms SQL SERVER:
The first secret is about the SELECT statement:
The SELECT statement in SQL can use another SELECT statement as the content source and perform nested processing according to this rule. However, there is a premise that an alias must be specified for the SELEC result set as the source, the following is an exemplary SQL statement. You can confirm it in the query Analyzer:
Select top 10 * from (select top 100 * from tblproducts where name like '% mp3 %' order by id desc) as a order by id desc please trust your eyes, the preceding statement can indeed be executed. You may have tried to execute a similar statement but failed because you did not specify an alias for The Source result set.
The second secret is a rule:
If you want to use the select top 20*4 method to retrieve the first 80 records in SQL, you will fail because the top clause has a limit, that is, the num in top num cannot be a calculated value or expression but must be an established constant.
The third secret is important enough. I think few people will find it. Otherwise, many problems will become simple. In short, it is: SQL allows you to execute SQL statements defined in a variable with execute, and allows you to nest a variable-defined statement in the executed SQL statement again, and execute it with execute again. If the language is not enough to prove it, the following code will prove it works and is good.
Declare @ sqlstr varchar (3000)
Set @ sqlstr = 'Clare @ subsqlstr varchar (1000 );'
Set @ sqlstr = @ sqlstr + 'set @ subsqlstr = ''select * From tblproducts '';'
Set @ sqlstr = @ sqlstr + 'execute (@ subsqlstr )'
Execute (@ sqlstr)
In the above Code, @ SQL variables define a group of SQL statements. In this group of SQL statements, another @ subsql variable is defined, where select statements are stored,
Therefore, when the system executes @ sqlstr, The Defined variables are executed. The execution result is that a group of dynamically executed SQL statements are constructed, saved to the variables, and finally, execute is executed again in execute. For this nested execution, let's call it dynamic execution capability.
However, it should be noted that:
1. Because the statement group is stored in the variable, you must add a semicolon (semicolon) to the end of each statement)
2. If the statement contains the value assignment of a variable, remember that you are writing a variable assignment statement. Therefore, remember to enclose the value with single quotation marks.
3. If you want to reference string constants in the statement group defined in the variable, replace single quotes with two single quotes.

Finally, I used the following methods to write two SQL General paging stored procedures:
They have the following features: 1. Field set selection and 2. Sorting of any field is supported,

In the preceding figure, we take the reverse order of the final result set as an example. In the first scheme, we first take the set OB, which can be completed using a statement using select top and upgrading the arrangement.
Then, the OB result set is sorted in reverse order, and the AB of the target set is obtained by "SELECT TOP records per page" in reverse order. The following describes the stored procedure.
Create PROCEDURE dbo. GetPagingData
(
@ Tablename varchar (100), -- table name or view chart
@ Fieldlist varchar (4000) = '*', -- List of fields to be selected
@ Orderfield varchar (100), -- Sort Field
@ Keyfield varchar (100), -- primary key
@ Pageindex int, -- page number, starting from 0
@ Pagesize int = 20, -- page size
@ Strwhere varchar (4000), -- Condition
@ Ordertype bit = 1 -- sort, 1, descending, 0, ascending
)
AS
SET NOCOUNT ON
Declare @ sqlstr varchar (6000)
-- Process dangerous characters in SQL and process conditions into easy-to-embed forms
Set @ sqlstr = 'Clare @ Rcount int ;'
Set @ sqlstr = @ sqlstr + 'set @ rcount = (select count ('+ @ keyfield +') from '+ @ tablename + 'where' + @ strWhere + ');'
Set @ strwhere = replace (@ strwhere ,'''','''''')
Set @ strwhere = replace (@ strwhere ,'--','')
Set @ strwhere = replace (@ strwhere ,';','')
Set @ sqlstr = @ sqlstr + 'Clare @ Rnum int ;'
Set @ sqlstr = @ sqlstr + 'set @ rnum = @ rcount-'+ cast (@ pagesize as varchar) +' * '+ cast (@ pageindex as varchar) + ';'
Set @ sqlstr = @ sqlstr + 'Clare @ sqlstr varchar (6000 );'
If @ ordertype = 1
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = ''select top ''+ cast (@ Pagesize as varchar) +'' + @ fieldlist + 'from (select top 100

Percent * from (select top ''+ cast (@ rnum as varchar) + ''* from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield + 'asc) as B order by paymoney desc) as a order by' + @ orderfield + 'desc '';'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = ''select top ''+ Cast (@ pagesize as varchar) +'' + @ fieldlist + 'from (select Top 100

Percent * from (select top ''+ Cast (@ rnum as varchar) + ''* from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield +' DESC) as B order by paymoney ASC) as a order by '+ @ orderfield + 'asc '';'
End
Set @ sqlstr = @ sqlstr + 'if @ rcount> 0 begin execute (@ sqlstr) end'
-- Print @ sqlstr
Execute (@ sqlstr)
In the above Code, no matching results are processed.
The idea of the second solution is as follows: first use the reverse select top (page number + 1) * page size method to obtain the AE result set, then, we use the not in method in the AE result set to exclude the result set obtained using the select top page number * page size method. Finally, we execute Reverse Order on the target result set, the following is the implementation code.
Create procedure DBO. getpagingrecord
(
@ Tablename varchar (100), -- table name or view chart
@ Fieldlist varchar (4000) = '*', -- List of fields to be selected
@ Orderfield varchar (100), -- Sort Field
@ Keyfield varchar (100), -- primary key
@ Pageindex int, -- page number, starting from 0
@ Pagesize int = 20, -- page size
@ Strwhere varchar (4000), -- Condition
@ Ordertype bit = 1 -- sort, 1, descending, 0, ascending
)
AS

SET NOCOUNT ON
Declare @ sqlstr varchar (6000)
-- Process dangerous characters in SQL and process conditions into easy-to-embed forms
Set @ strwhere = replace (@ strwhere ,'''','''''')
Set @ strwhere = replace (@ strwhere ,'--','')
Set @ strwhere = replace (@ strwhere ,';','')
Set @ sqlstr = 'Clare @ CurPageNum int ;'
Set @ sqlstr = @ sqlstr + 'Clare @ nextpagenum int ;'
Set @ sqlstr = @ sqlstr + 'set @ curpagenum = '+ cast (@ PageIndex as varchar) +' * '+ cast (@ Pagesize as varchar) + ';'
Set @ sqlstr = @ sqlstr + 'set @ nextpagenum = '+ cast (@ PageIndex + 1 as varchar) +' * '+ cast (@ Pagesize as varchar) + ';'
Set @ sqlstr = @ sqlstr + 'Clare @ sqlstr varchar (6000 );'
If @ ordertype = 1
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = 'select' + @ fieldlist + 'from (select top ''+ cast (@ nextpagenum as varchar) + ''' * from

'+ @ Tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'desc) as a where' + @ keyfield +' not in (

Select top ''+ cast (@ curpagenum as varchar) + ''' + @ keyfield + 'from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield + 'desc') order by' + @ orderfield + 'desc '';'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'set @ sqlstr = 'select' + @ fieldlist + 'from (select top ''+ cast (@ nextpagenum as varchar) + ''' * from

'+ @ Tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'asc) as a where' + @ keyfield +' not in (

Select top ''+ Cast (@ curpagenum as varchar) + ''' + @ keyfield + 'from' + @ tablename + 'where' + @ strwhere +'

Order by '+ @ orderfield + 'asc) order by' + @ orderfield + 'asc '';'
End
Set @ sqlstr = @ sqlstr + 'execute (@ sqlstr )'
-- Print @ sqlstr
Execute (@ sqlstr)
Note that if you want to avoid SQL injection attacks, you must handle characters with special meanings such as semicolons, double minus signs, and single quotes.

Which of the above two stored procedures has better performance depends on the reverse order of a result set, we recommend that you use not in to remove a small result set from a large result set.