Mssqlserversql paging Stored Procedure

A simple SQL distribution code and a general SQL. Setquoted_identifierongosetansi_nullsongocreateprocexecbypage @ sqlqueryvarchar (2000), -- input parameter: SQL search statement or table name @ pagesizeint, -- input parameter: number of records per page display @ page a simple SQL distribution code, it is also a general SQL.

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

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