SQL Server efficient Stored Procedure paging (max/min method)

Drop procedure propageset
Go

Create procedure propageset
@ TB varchar (50), -- table name
@ Col varchar (50), -- pagination by this column (it must be a unique character, such as the identification seed)
@ Colorder varchar (50), -- the field to be sorted (if it is null, the default value is col)
@ Orderby bit, -- sort, 0-order, 1-inverted
@ Collist varchar (800), -- List of fields to be queried, * indicates all fields
@ Pagesize int, -- number of records per page
@ Page int, -- specify the page
@ Condition varchar (800), -- Query Condition
@ Pages int output -- total number of pages
-- @ Sqlout nvarchar (4000), --- return the SQL statement

As
Set nocount on
Declare @ intresult int
Begin tran
Declare @ SQL nvarchar (4000), @ where1 varchar (800), @ where2 varchar (800)
If @ colorder is null or rtrim (@ colorder) =''
Set @ colorder = @ col
If @ condition is null or rtrim (@ condition) =''
Begin -- no query Conditions
Set @ where1 = 'where'
Set @ where2 =''
End
Else
Begin -- with query Conditions
Set @ where1 = 'where ('+ @ condition +') and '-- this condition is added if conditions exist.
Set @ where2 = 'where ('+ @ condition +') '-- this condition is added if no conditions exist.
End
Set @ SQL = 'select @ intresult = count (*) from' + @ TB + @ where2
Exec sp_executesql @ SQL, n' @ intresult int output', @ intresult output -- calculate the total number of records
Select @ pages = ceiling (@ intresult + 0.0)/@ pagesize) -- calculate the total number of pages
If @ orderby = 0
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist + 'from' + @ TB + @ where1 + @ Col + '> (select max (' + @ Col + ') '+' from (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + ') T) Order by' + @ colorder
Else
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist + 'from' + @ TB + @ where1 + @ Col + '<(select Min (' + @ Col + ') '+' from (select top '+ Cast (@ pagesize * (@ page-1) as varchar) + ''+
@ Col + 'from' + @ TB + @ where2 + 'ORDER BY' + @ Col + 'desc) T) order by '+ @ colorder + 'desc'
If @ page = 1 -- first page
Set @ SQL = 'select top '+ Cast (@ pagesize as varchar) + ''+ @ collist + 'from' + @ TB +
@ Where2 + 'ORDER BY' + @ colorder + case @ orderby when 0 then ''else 'desc' end
-- Set @ sqlout = @ SQL
Exec (@ SQL)
-- Print 'SQL statement output:' + @ sqlout
Certificate -------------------------------------------------------------------------------------------------------------------------------------------
If @ error <> 0
Begin
Rollback tran
Return-1
End
Else
Begin
Commit tran
Return @ intresult
End
Go

Call:
Example: Create the stored procedure in pubs.

ASP call example: Paging the employee table

Table Structure
Create Table [DBO]. [employee] (
[Emp_id] [empid] not null,
[Fname] [varchar] (20) Collate chinese_prc_ci_as not null,
[Minit] [char] (1) Collate chinese_prc_ci_as null,
[Lname] [varchar] (30) Collate chinese_prc_ci_as not null,
[Job_id] [smallint] not null,
[Job_lvl] [tinyint] Null,
[Pub_id] [char] (4) Collate chinese_prc_ci_as not null,
[Hire_date] [datetime] not null
) On [primary]
Go

CodeFile:

Set cmd = server. Createobject ("ADODB. Command ")
With cmd
. Activeconnection = conn' database connection string
. Commandtext = "propageset" 'specifies the name of the stored procedure.
. Commandtype = 4' indicates that this is a stored procedure
. Prepared = true' requires that the SQL command be compiled first
. Parameters. append. createparameter ("return", 3,4, 4) 'Return Value
. Parameters. append. createparameter ("@ TB", 1, 50, "t_admin") 'indicates the name of the table to be queried.
. Parameters. append. createparameter ("@ col", 1, 50, "f_id") 'by this column for paging
. Parameters. append. createparameter ("@ colorder", 1, 50, orderfield) 'sorting Field
. Parameters. append. createparameter ("@ orderby", 1, orderway) 'sorting method. 0 indicates order, and 1 indicates reverse order.
. Parameters. append. createparameter ("@ collist", 800, "*") 'fields to be displayed on each page, separated by commas
. Parameters. append. createparameter ("@ pagesize", 3, 1, 4, mypagesize) 'number of records per page
. Parameters. append. createparameter ("@ page", 4, page) 'specifies the number of pages
. Parameters. append. createparameter ("@ condition", 800, sqlwhere) 'condition statement in the query condition where
. Parameters. append. createparameter ("@ pages", 4) 'total page output
Set rs =. Execute
End

While not Rs. EOF
'Display the output page of the database content
Rs. movenext
Wend

Rs. Close 'must be closed before getting the return value; otherwise, the return value cannot be obtained.
Totalrecord = cmd (0) 'total number of records
Totalpage = cmd (9) 'total number of pages

Set rs = nothing
Set cmd = nothing

The generated SQL statement is actually like this: (five records on each page, and the third page)

Select top 5 * From t_admin where f_id>
(Select max (f_id) from
(Select top 10 f_id from t_admin order by f_id) T)
Order by f_username DESC