A paging Stored Procedure of SQL Server 2000

From: http://www.devdao.com/Article/344500.htm

It has the following advantages: simple, direct SQL statement input, efficient, and highly efficient tests. However, it is best to sort the primary key and use less views.

Disadvantages: complex SQL statements, multi-field sorting, and SQL statements must be less than 4000 characters

/*
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: sunjianhua_kki@sina.com
Created:
Modification time:
Http://blog.knowsky.com/
*/
Create procedure getrecordfrompage1
@ SQL varchar (8000), -- SQL statement
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
-- @ Strout varchar (6000) Output SQL statement processed
@ Recordcount Int = 0 output -- total number of output records
As

Declare @ strsql varchar (8000) -- The last obtained SQL statement
Declare @ strtmp varchar (8000) -- Temporary Variable
Declare @ strorder varchar (500) -- sort type
Declare @ tblname varchar (255) -- table name
Declare @ fldname varchar (255) -- field name
Declare @ strwhere varchar (8000) -- Query condition (Note: Do not add where)
Declare @ tmporder varchar (255) -- Sort string temporary variables
Declare @ strfilds varchar (8000) -- column to be displayed
Declare @ intfilds int -- position of the displayed Column
Declare @ intorder int -- position of the sorted string
Declare @ intsql int -- SQL statement Length
Declare @ intwhere int -- Where string position
Declare @ inttable int -- table name location
Declare @ strrssql nvarchar (4000) -- SQL statement for counting the total number of records

Set @ intorder = charindex ('ORDER BY', @ SQL)
Set @ intsql = Len (@ SQL)

Set @ intfilds = charindex ('select', @ SQL)
Set @ strfilds = substring (@ SQL, @ intfilds + 7, @ intsql-@ intFilds-1)
Set @ strfilds = substring (@ strfilds, 1, charindex ('from', @ strfilds)-1)

Set @ tblname = substring (@ SQL, charindex ('from', @ SQL) + 5, @ intsql-charindex ('from', @ SQL) + 5)
Set @ inttable = charindex ('', @ tblname)
If @ inttable> 0
Begin
Set @ tblname = substring (@ tblname, 1, @ intTable-1)
End

Set @ intwhere = charindex ('where', @ SQL)
If @ intwhere> 0
Begin
If @ intorder> 0
Begin
Set @ strwhere = substring (@ SQL, @ intwhere + 6, @ intorder-@ intWhere-7)
End
Else
Begin
Set @ strwhere = substring (@ SQL, @ intwhere + 6, @ intsql-@ intWhere-5)
End
End
Else
Begin
Set @ strwhere =''
End

If @ intorder> 0
Begin
Set @ strorder = ''+ substring (@ SQL, @ intorder, @ intsql-@ intorder + 1)
Set @ tmporder = substring (@ strorder, 11, Len (@ strorder)-10)
If charindex ('', @ tmporder)> 0
Begin
Set @ fldname = substring (@ tmporder, 1, charindex ('', @ tmporder)-1)
End
Else
Begin
Set @ fldname = @ tmporder
End
If charindex ('desc', @ SQL)> 0
Begin
Set @ strtmp = '<(select min'
End
Else
Begin
Set @ strtmp = '> (select Max'
End
End
Else
Begin
Set @ fldname = 'id'
Set @ strtmp = '> (select Max'
Set @ strorder = 'order by ['+ @ fldname +'] ASC'
End

Set @ strsql = 'select top' + ltrim (STR (@ pagesize) + ''+ @ strfilds + 'from ['
+ @ Tblname + '] Where [' + @ fldname + ']' + @ strtmp + '(['
+ @ Fldname + ']) from (select top' + ltrim (STR (@ PageIndex-1) * @ pagesize) + '['
+ @ Fldname + '] from [' + @ tblname + ']' + @ strorder + ') as tbltmp )'
+ @ Strorder

If @ strwhere! =''
Set @ strsql = 'select top' + ltrim (STR (@ pagesize) + ''+ @ strfilds + 'from ['
+ @ Tblname + '] Where [' + @ fldname + ']' + @ strtmp + '([' + @ fldname + ']) from (select top '+ ltrim (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' + ltrim (STR (@ pagesize) + ''+ @ strfilds + 'from ['
+ @ Tblname + ']' + @ strtmp + ''+ @ strorder
End

Exec (@ strsql)

----- Count the total number of records
If @ strwhere! =''
Begin
Set @ strrssql = 'select @ recordcount = count ('+ @ fldname +') from ['+ @ tblname
+ '] Where (' + @ strwhere + ')'
End
Else
Begin
Set @ strrssql = 'select @ recordcount = count ('+ @ fldname +') from ['+ @ tblname
+ ']'
End
Exec sp_executesql @ strrssql, n' @ recordcount int out', @ recordcount out
----- Statistics end

-- Set @ Strout = @ strsql
Go