Display database stored procedures by PAGE

Note: You need to create an index.
Copy codeThe Code is as follows:
/*
After testing, 14483461 pages are queried in 100,000th records. The first and second time of 10 records on each page is 0.47 seconds in ascending and descending order, and the second time is 0.43 seconds. The test syntax is as follows:
Exec GetRecordFromPage news, newsid, 10, 100000
News is the table name and newsid is the key field. Create an index for newsid before use.
*/

/*
Function Name: GetRecordFromPage
Function: obtains data on a specified page.
Parameter description: @ tblName indicates the name of the table containing data.
@ FldName key field name
@ PageSize number of records per page
@ PageIndex: page number to be obtained
@ OrderType: Sorting type. Values: 0 (ascending) and 1 (descending ).
@ StrWhere query condition (Note: Do not add where)
Prepared by: Tie Quan
Mail Box: unjianhua_kki@sina.com "> sunjianhua_kki@sina.com
Created:
Modification time:
*/
Create PROCEDURE GetRecordFromPage
@ TblName varchar (255), -- table name
@ FldName varchar (255), -- field name
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (2000) = ''-- Query condition (Note: Do not add where)
AS

Declare @ strSQL varchar (6000) -- subject sentence
Declare @ strTmp varchar (1000) -- Temporary Variable
Declare @ strOrder varchar (500) -- 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

Exec (@ strSQL)

GO