Create a stored procedure

In the query window, enter:

Set ansi_nulls on
Go
Set quoted_identifier on
Go
If not exists (select * From sys. objects where object_id = object_id (n' [DBO]. [getlistbypage] ') and type in (n'p', n'pc '))
Begin
Exec DBO. sp_executesql @ Statement = N'

-- Efficient paging Stored Procedure
-- Create by Jinlong Zhang

Create procedure [DBO]. [getlistbypage] (
@ table varchar (500), -- table name
@ field varchar (500) = ''*'', -- read field
@ where varchar (500) = NULL, -- Where condition
@ groupby varchar (500) = NULL, -- group
@ orderby varchar (500) = NULL, -- Sort field
@ primarykeyfield varchar (50), -- primary key required
@ pageindex Int = 1, -- start page number
@ pagesize Int = 10, -- page size
@ iscount bit = 0 -- total number of returned records

Bytes ------------------------------------------------------------------------------------------------
-- When @ iscount is 1, two tables are returned at the same time. Table 0 indicates the total number of records, and table 1 indicates the query result.
Bytes ------------------------------------------------------------------------------------------------

)
As
Begin
Bytes ------------------------------------------------------------------------------------------------
Declare @ strwhere nvarchar (500) -- where Condition
If @ Where is not null and @ where! = ''' -- Where Condition
Begin
Set @ strwhere = ''where'' + @ where + ''''
End
Else
Begin
Set @ strwhere = ''''
End
Bytes ----------------------------------------------------------------------------------------------------
Declare @ strgroupby nvarchar (500) -- groupby Condition
If @ groupby is not null and @ groupby! = ''' -- Groupby Condition
Begin
Set @ strgroupby = ''group by'' + @ groupby + ''''
End
Else
Begin
Set @ strgroupby = ''''
End
Bytes ----------------------------------------------------------------------------------------------------
Declare @ strorderby nvarchar (500) -- orderby Condition
If @ orderby is null or @ orderby = ''' -- orderby Condition
Begin
Set @ strorderby = ''order by'' + @ primarykeyfield + ''desc''
End
Else
Begin
Set @ strorderby = ''order by'' + @ orderby
End
Bytes ----------------------------------------------------------------------------------------------------
Declare @ strsql nvarchar (max) -- SQL statement
-- Calculate the total number of rows
If @ iscount = 1
Begin
Set @ strsql = ''select count (*) as recordcount from ''+ @ table + @ strwhere + @ strgroupby
Exec sp_executesql @ strsql
-- Return
End

Bytes ----------------------------------------------------------------------------------------------------
If @ pageindex <1 -- the first page improves performance
Begin
Set @ pageindex = 1
End

If @ pageindex = 1
Begin
Set @ strsql = ''select top ''+ STR (@ pagesize) + '''' + @ field + ''from'' + @ table +
@ Strwhere + @ strgroupby + @ strorderby
Exec sp_executesql @ strsql
Return
End
Bytes ----------------------------------------------------------------------------------------------------
Declare @ startid nvarchar (50)
Declare @ endid nvarchar (50)
Set @ startid = convert (nvarchar (50), (@ pageindex-1) * @ pagesize + 1)
Set @ endid = convert (nvarchar (50), @ pageindex * @ pagesize)
Set @ strsql = ''with mytable as (select row_number () over ('' + @ strorderby + '')
As rownumber, ''+ @ field + ''from'' + @ table + @ strwhere + @ strgroupby + '')
Select * From mytable
Where rownumber between ''+ @ startid +'' and ''+ @ endid
Exec sp_executesql @ strsql
Bytes --------------------------------------------------------------------------------------------------
End

 

'
End
Go
Set ansi_nulls on
Go
Set quoted_identifier on
Go
If not exists (select * From SYS. objects where object_id = object_id (n' [DBO]. [qiwen] ') and type in (n'u '))
Begin
Create Table [DBO]. [qiwen] (
[ID] [int] identity (1, 1) not null,
[Title] [varchar] (100) not null,
[Partition title] [varchar] (50) null
) On [primary]
End