An efficient paging data storage process can easily handle millions of data records

ProgramCode

Create procedure pagetest -- used for paging Testing
-- Put the sorting field in the first column
(
@ Firstid nvarchar (20) = NULL, -- the value of the sorting field of the first record on the current page
@ Lastid nvarchar (20) = NULL, -- Value of the sorting field of the last record on the current page
@ Isnext bit = NULL, -- true 1: Next page; false 0: Previous Page
@ Allcount int output, -- returns the total number of records
@ Pagesize int output, -- returns the number of records on a page
@ Curpage int -- page number (page number) 0: First page;-1 last page.
)

As

If @ curpage = 0
Begin
-- Count the total number of records
Select @ allcount = count (productid) from product_test

Set @ pagesize = 10
-- Return the data on the first page
Select top 10
Productid,
Productname,
Introduction
From product_test order by productid
End

Else if @ curpage =-1

Select * from
(Select top 10 productid,
Productname,
Introduction

From product_test order by productid DESC) as AA
Order by productid
Else

Begin
If @ isnext = 1
-- Go to the next page
Select top 10 productid,
Productname,
Introduction
From product_test where productid> @ lastid order by productid

Else
-- Go to the previous page
Select * from
(Select top 10 productid,
Productname,
Introduction
From product_test where productid <@ firstid order by productid DESC) as BB order by productid
End

II

-- Get data on a specified page
Create procedure pagination
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = '', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Docount 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 (1500) = ''-- Query condition (Note: Do not add where)
As

Declare @ strsql varchar (5000) -- subject sentence
Declare @ strtmp varchar (110) -- Temporary Variable
Declare @ strorder varchar (400) -- sort type
If @ docount! = 0
Begin
If @ strwhere! =''
Set @ strsql = "select count (*) as total from [" + @ tblname + "] Where" + @ strwhere
Else
Set @ strsql = "select count (*) as total from [" + @ tblname + "]"
End
-- The above Code indicates that if @ docount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ docount

Else
Begin
If @ ordertype! = 0
Begin
Set @ strtmp = "<(select Min"
Set @ strorder = "order by [" + @ fldname + "] DESC"
-- If @ ordertype is not 0, execute the descending order. This sentence is very important!
End

Else
Begin
Set @ strtmp = "> (select Max"
Set @ strorder = "order by [" + @ fldname + "] ASC"
End
If @ pageindex = 1
Begin
If @ strwhere! =''
Set @ strsql = "select top" + STR (@ pagesize) + "" + @ strgetfields + "from [" + @ tblname + "] Where" + @ strwhere + "" + @ strorder
Else
Set @ strsql = "select top" + STR (@ pagesize) + "" + @ strgetfields + "from [" + @ tblname + "]" + @ strorder
-- Execute the above Code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strsql the SQL code to be actually executed
Set @ strsql = "select top" + STR (@ pagesize) + "" + @ strgetfields + "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) + "" + @ strgetfields + "from ["
+ @ Tblname + "] Where [" + @ fldname + "]" + @ strtmp + "(["
+ @ Fldname + "]) from (select top" + STR (@ PageIndex-1) * @ pagesize) + "["
+ @ Fldname + "] from [" + @ tblname + "] Where" + @ strwhere + ""
+ @ Strorder + ") as tbltmp) and" + @ strwhere + "" + @ strorder
End
End

Exec (@ strsql)
Go