SQL paging Data Query

In many database application systems, a large amount of data query operations are performed. When the data volume to be queried is large, the query time will be slow, so we hope to obtain data in batches.

A stored procedure is used to obtain data in batches. The cursor is not used because of its low efficiency.

Create procedure selectpage @ tablename nvarchar (64), @ keyfield nvarchar (64), @ fields nvarchar (512), @ pageindex int, @ pagesize Int = 15
As
Declare @ SQL nvarchar (512)
If @ pageindex = 1 begin
Set @ SQL = 'select top '+ convert (nvarchar (8), @ pagesize) + ''+ @ fields + 'from' + @ tablename
End
Else begin
Set @ SQL = 'select top '+ convert (nvarchar (8), @ pagesize) + ''+ @ fields + 'from' + @ tablename
Set @ SQL = @ SQL + 'where' + @ keyfield + '> (select max (' + @ keyfield + ') from (select top'
Set @ SQL = @ SQL + convert (nvarchar (8), @ pagesize * (@ pageindex-1 )) + '* from' + @ tablename + 'ORDER BY' + @ keyfield +') T )'
End

-- Print @ SQL
Exec sp_executesql @ SQL

Parameter description:

@ Tablename: name of the data table to be queried

@ Keyfield: primary key field of the data table

@ Fields: List of field names to be queried, separated by commas (,). The last field name does not require a comma.

@ Pageindex: The index of the page to be queried.

@ Pagesize: number of data records per page. The default value is 15, that is, 15 data records per page.