Stored Procedures | pagination | data/*
After testing, in 14,483,461 records to query the 100,000th page, each page 10 records in ascending and descending order the first time is 0.47 seconds, the second time is 0.43 seconds, the test syntax is as follows:
EXEC getrecordfrompage news,newsid,10,100000
News is the table name, NewSID is the key field, and the NewSID index is used first.
*/
/*
Function Name: getrecordfrompage
function function: Gets the data for the specified page
Parameter description: @tblName The table name that contains the data
@fldName key field names
@PageSize number of records per page
@PageIndex the page number to get
@OrderType sort type, 0-Ascending, 1-Descending
@strWhere Query Criteria (Note: Do not add where)
Author: Iron Fist
Email: sunjianhua_kki@sina.com
Date Created: 2004-07-04
Modification Date: 2004-07-04
*/
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 sort type, not 0 value descending
@strWhere varchar (2000) = '--Query criteria (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 + ') '