2-way paging Stored Procedure script instance

It should be noted that there are many parameters for this stored procedure. In actual use, I wrote a class separately outside, and called the encapsulated class directly by calling the page. There are many methods, the main idea is for your reference.

Code modifications are concentrated in a similar
Copy codeThe Code is as follows:
If @ Sort = 0
Set @ strTmp = @ strTmp + '<(select min ('
Else
Set @ strTmp = @ strTmp + '> (select max ('

In addition, the 94 lines mainly work with the classes I wrote to display information such as the number of records and the number of pages. If you do not need them, remove them.

Copy codeThe Code is as follows:
1 alter procedure [dbo]. [proc_ListPage]
2 (
3 @ tblName nvarchar (200), ---- tables to be displayed or connections to multiple tables
4 @ fldName nvarchar (500) = '*', ---- list of fields to be displayed
5 @ pageSize int = 10, ---- number of records displayed on each page
6 @ page int = 1, ---- the record of the page to be displayed
7 @ fldSort nvarchar (200) = null, ---- list of sorting fields or conditions
8 @ Sort bit = 0, ---- sorting method. 0 indicates ascending order, 1 is in descending order (for multi-field sorting, Sort refers to the sorting order of the last sorting field (the last sorting field is not marked with a sorting mark)-the Program transmits the parameter as follows: 'sorta Asc, sortB Desc, SortC ')
9 @ strCondition nvarchar (1000) = null, ---- query condition, where is not required
10 @ ID nvarchar (150), ---- primary key of the master table
11 @ Dist bit = 0, ---- whether to add the DISTINCT of the query field. By default, 0 is not added./1 is added.
12 @ pageCount int = 1 output, ---- the total number of pages after the query results page
13 @ Counts int = 1 output ---- number of records queried
14)
15
16 SET NOCOUNT ON
17 Declare @ sqlTmp nvarchar (1000) ---- stores dynamically generated SQL statements
18 Declare @ strTmp nvarchar (1000) ---- stores the query statement for obtaining the total number of query results
19 Declare @ strID nvarchar (1000) ---- A query statement for obtaining the start or end ID of a query
20
21 Declare @ strSortType nvarchar (10) ---- data sorting Rule
22 Declare @ strFSortType nvarchar (10) ---- data sorting rule B
23
24 Declare @ SqlSelect nvarchar (50) ---- SQL construction for queries containing DISTINCT
25 Declare @ SqlCounts nvarchar (50) ---- SQL structure of the total number of queries containing DISTINCT
26
27
28 if @ Dist = 0
29 begin
30 set @ SqlSelect = 'select'
31 set @ SqlCounts = 'count (0 )'
32 end
33 else
34 begin
35 set @ SqlSelect = 'select distinct'
36 set @ SqlCounts = 'count (DISTINCT '+ @ ID + ')'
37 end
38
39
40 if @ Sort = 0
41 begin
42 set @ strFSortType = 'asc'
43 set @ strSortType = 'desc'
44 end
45 else
46 begin
47 set @ strFSortType = 'desc'
48 set @ strSortType = 'asc'
49 end
50
51
52
53 -------- generate query statement --------
54 -- Here @ strTmp is the statement for obtaining the number of query results
55 if @ strCondition is null or @ strCondition = ''-- no display conditions are set.
56 begin
57 set @ sqlTmp = @ fldName + 'from' + @ tblName
58 set @ strTmp = @ SqlSelect + '@ Counts =' + @ SqlCounts + 'from' + @ tblName
59 set @ strID = 'from' + @ tblName
60 end
61 else
62 begin
63 set @ sqlTmp = + @ fldName + 'from' + @ tblName + 'where (1> 0) '+ @ strCondition
64 set @ strTmp = @ SqlSelect + '@ Counts =' + @ SqlCounts + 'from' + @ tblName + 'where (1> 0) '+ @ strCondition
65 set @ strID = 'from' + @ tblName + 'where (1> 0) '+ @ strCondition
66 end
67
68 ---- total number of query results retrieved -----
69 exec sp_executesql @ strTmp, n' @ Counts int out', @ Counts out
70 declare @ tmpCounts int
71 if @ Counts = 0
72 set @ tmpCounts = 1
73 else
74 set @ tmpCounts = @ Counts
75
76 -- retrieve the total number of pages
77 set @ pageCount = (@ tmpCounts + @ pageSize-1)/@ pageSize
78
79/** // ** select the last page if the current page is greater than the total page number **/
80 if @ page> @ pageCount
81 set @ page = @ pageCount
82
83 --/* ----- 2-point data processing by PAGE -------*/
84 declare @ pageIndex int -- total number/page size
85 declare @ lastcount int -- Total % page size
86
87 set @ pageIndex = @ tmpCounts/@ pageSize
88 set @ lastcount = @ tmpCounts % @ pageSize
89 if @ lastcount> 0
90 set @ pageIndex = @ pageIndex + 1
91 else
92 set @ lastcount = @ pagesize
93
94 -- for cooperative display
95 set nocount off
96 select @ page curpage, @ pageSize pagesize, @ pageCount countpage, @ tmpCounts [Rowcount]
97 set nocount on
98
99 -- // **** display the page
100 if @ strCondition is null or @ strCondition = ''-- no display conditions are set.
101 begin
102 if @ pageIndex <2 or @ page <= @ pageIndex/2 + @ pageIndex % 2 -- first half Data Processing
103 begin
104 if @ page = 1
105 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40) + ''+ @ fldName + 'from' + @ tblName
106 + 'ORDER BY' + @ fldSort + ''+ @ strFSortType
107 else
108 begin
109 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40) + ''+ @ fldName + 'from' + @ tblName
110 + 'where' + @ ID
111 if @ Sort = 0
112 set @ strTmp = @ strTmp + '> (select max ('
113 else
114 set @ strTmp = @ strTmp + '<(select min ('
115 set @ strTmp = @ strTmp + @ ID + ') from (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20) + ''+ @ ID + 'from' + @ tblName
116 + 'ORDER BY' + @ fldSort + ''+ @ strFSortType + ') AS TBMinID )'
117 + 'ORDER BY' + @ fldSort + ''+ @ strFSortType
118 end
119 end
120 else
121
122 begin
123 set @ page = @ pageIndex-@ page + 1 -- data processing in the second half
124 if @ page <= 1 -- display the last page of data
125 set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ lastcount as VARCHAR (40 )) + ''+ @ fldName + 'from' + @ tblName
126 + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS temptb' + 'ORDER BY' + @ fldSort +'' + @ strFSortType
127 else
128 begin
129 set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40 )) + ''+ @ fldName + 'from' + @ tblName
130 + 'where' + @ ID
131 if @ Sort = 0
132 set @ strTmp = @ strTmp + '<(select min ('
133 else
134 set @ strTmp = @ strTmp + '> (select max ('
135 set @ strTmp = @ strTmp + @ ID + ') from (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20) + ''+ @ ID + 'from' + @ tblName
136 + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS TBMaxID )'
137 + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS temptb' + 'ORDER BY' + @ fldSort +'' + @ strFSortType
138 end
139 end
140
141 end
142
143 else -- with query Conditions
144 begin
145 if @ pageIndex <2 or @ page <= @ pageIndex/2 + @ pageIndex % 2 -- first half Data Processing
146 begin
147 if @ page = 1
148 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40) + ''+ @ fldName + 'from' + @ tblName
149 + 'where 1 = 1' + @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strFSortType
150 else
151 begin
152 set @ strTmp = @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40) + ''+ @ fldName + 'from' + @ tblName
153 + 'where' + @ ID
154 if @ Sort = 0
155 set @ strTmp = @ strTmp + '> (select max ('
156 else
157 set @ strTmp = @ strTmp + '<(select min ('
158
159 set @ strTmp = @ strTmp + @ ID + ') from (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-1) as Varchar (20) + ''+ @ ID + 'from' + @ tblName
160 + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strFSortType +') AS TBMinID )'
161 + ''+ @ strCondition + 'ORDER BY' + @ fldSort +'' + @ strFSortType
162 end
163 end
164 else
165 begin
166 set @ page = @ pageIndex-@ page + 1 -- data processing in the second half
167 if @ page <= 1 -- display the last page of data
168 set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ lastcount as VARCHAR (40 )) + ''+ @ fldName + 'from' + @ tblName
169 + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType
170 else
171 begin
172 set @ strTmp = @ SqlSelect + '* from (' + @ SqlSelect + 'top' + CAST (@ pageSize as VARCHAR (40 )) + ''+ @ fldName + 'from' + @ tblName
173 + 'where' + @ ID
174 if @ Sort = 0
175 set @ strTmp = @ strTmp + '<(select min ('
176 else
177 set @ strTmp = @ strTmp + '> (select max ('
178 set @ strTmp = @ strTmp + @ ID + ') from (' + @ SqlSelect + 'top' + CAST (@ pageSize * (@ page-2) + @ lastcount as Varchar (20) + ''+ @ ID + 'from' + @ tblName
179 + 'where (1 = 1) '+ @ strCondition + 'ORDER BY' + @ fldSort + ''+ @ strSortType +') AS TBMaxID )'
180 + ''+ @ strCondition + 'ORDER BY' + @ fldSort +'' + @ strSortType + ') AS TempTB '+ 'ORDER BY' + @ fldSort + ''+ @ strFSortType
181 end
182 end
183
184 end
185
186 ------ return query result -----
187 set nocount off
188 exec sp_executesql @ strTmp
189 print @ strTmp