Switch: SQL server data paging storage process

  Normally SQL When the number of data items in a query statement is small, the speed will be slower, because it takes a lot of time to transmit a large amount of data over the network. If you query data by PAGE, only useful data is returned. After you exclude unnecessary data, the transmission speed is much faster. SQL Paging stored procedure. This process is based on SQL Built-in Northwind The database is written as an example and can be changed as needed.

Create Procedure [Getcustomersdatapage]
@ Pageindex Int , -- Page number
@ Pagesize Int , -- Page number
@ Recordcount Int Out , -- Number of records
@ Pagecount Int Out -- Page number
As
Select @ Recordcount = Count (*) From MERs
Set @ Pagecount = Ceiling (@ Recordcount * 1.0/@ pagesize)

Declare@ SqlstrNvarchar(1000)

If @ Pageindex = 0 Or @ Pagecount <= 1
Set @ Sqlstr = 'Select top' + Str (@ Pagesize) + 'Mermerid, companyName, address, phone from MERs order by mermerid DESC'

Else If @ Pageindex = @ pagecount-1
Set @ Sqlstr = 'Select * from (select top' + Str (@ Recordcount-@ pagesize * @ pageindex) + 'Mermerid, companyName, address, phone from MERs order by customerid ASC) temptable order by mermerid DESC'

Else
Set @ Sqlstr = 'Select top' + Str (@ Pagesize) + '* From (select top' + Str (@ Recordcount-@ pagesize * @ pageindex) + 'Mermerid, companyName, address, phone from MERs order by customerid ASC) temptable order by mermerid DESC'

Exec(@ Sqlstr)

Go