SQL Server's general paging stored procedure does not use a cursor, which is faster!

When using SQLServer, paging processing has always been a tough issue.

When using SQL Server, paging processing has always been a tough issue.

Under normal circumstances, the SQL Server will create an appropriate index for a frequently used Table
This will greatly improve the data retrieval speed of the database itself, and the indexing method will not be detailed.

If you need to return a large amount of data, from several hundred rows to tens of thousands of rows, or even hundreds of thousands of rows
At this time, you will find that the response speed is getting slower and slower, and even the response timeout error occurs.
In order to solve this big data request problem, we have to use the paging mode.

In this regard, JDBC is much more powerful. It can send the specified number of rows along with the SQL request to the SQL Server. In this way, only the paging data is returned. The principle of JDBC is unclear, however, in actual use, the speed is still very fast.

If you cannot use JDBC, the most common method is the stored procedure!

Before writing this paging storage, I have referenced a large number of related articles on the Internet and can search by Keyword: SQL Server paging.
They mainly use the Top method in SQL and require an ID column for The retrieved data structure. If there is no ID column or a joint primary key, it will be very troublesome. In addition, the original SQL search part of the application needs to be modified in many places, resulting in a large workload.

Therefore, before writing this storage, I must be compatible with the original SQL script to the maximum extent.

After one afternoon, I worked with one of my colleagues (absolutely a master) to find out the following ideas:

1. Determine the storage input parameters:
1) SQL script. This parameter receives complete and correct SQL retrieval text. You can directly input the SQL script written in the original application.
2) The data capacity on each page is the number of data records on a page.
3) Current page number
2. Determine the paging mechanism:
1) execute the input SQL script and generate a temporary table
2) modify the structure of the temporary table and add an ID column Field
3) Calculate the record range in the specified page number based on the field of the identification column, and return
4) the total number of returned data entries for the client to display by PAGE

Based on the above ideas, compile the following general paging stored procedures:
The Code is as follows:
[Code]
-- // ==================================
-- // SQL Server General paging Stored Procedure
-- // Author: netwild
-- // Date: 2010/07/22
-- // Email: netwild@163.com
-- // QQ: 52100641)
-- // ==================================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


Create proc execByPage

@ SqlQuery varchar (2000), -- // input parameter: SQL search statement or table name
@ PageSize int, -- // input parameter: number of records per page
@ PageIndex int -- // input parameter: Current page number

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @ tmpTableName varchar (50)
Set @ tmpTableName = '# TB1516 _' + replace (cast (newid () as varchar (40), '-', '') -- // generate a random temporary table name

Declare @ subIndex int
Set @ subIndex = charindex ('from', @ sqlQuery)
If (@ subIndex> 0)
Begin -- // standard search statement with FROM
Declare @ sqlQuery1 varchar (2000)
Declare @ sqlQuery2 varchar (2000)
Set @ sqlQuery1 = substring (@ sqlQuery, 1, @ subIndex-1)
Set @ sqlQuery2 = substring (@ sqlQuery, @ subIndex, len (@ sqlQuery ))
Set @ sqlQuery = @ sqlQuery1 + ', IDENTITY (numeric, 1, 1) as ID1516 into' + @ tmpTableName + ''+ @ sqlQuery2
End
Else -- // table name without FROM
Begin
Set @ sqlQuery = 'select *, IDENTITY (numeric, 1, 1) as ID1516 into '+ @ tmpTableName + 'from' + @ sqlQuery
End
Exec (@ sqlQuery) -- // create and initialize temporary table data

Declare @ indexStart varchar (20), @ indexEnd varchar (20)
Set @ indexStart = cast (@ pageIndex-1) * @ pageSize + 1 as varchar (20) -- // data start row ID
Set @ indexEnd = cast (@ pageIndex * @ pageSize as varchar (20) -- // data end row ID

Exec ('select * from' + @ tmpTableName + 'where ID1516 between' + @ indexStart + 'and' + @ indexEnd) -- // retrieve the page data

Exec ('select max (ID1516) as recordCount from '+ @ tmpTableName) -- // extract the total number of entries

Exec ('drop table' + @ tmpTableName) -- // delete a temporary table


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/Code]