Need to explain: this stored process parameters are more, I actually use in the outside and write a separate class, the page calls the package directly call the class, there are many methods, mainly thinking, we can refer to.
Code modifications are concentrated in a similar
Copy Code code as follows:
If @Sort =0
Set @strTmp = @strTmp + ' < select min ('
Else
Set @strTmp = @strTmp + ' > (select Max ('
The other 94 lines are mainly in line with my own writing class, showing the number of pages of records and other information, if you do not need to remove.
Copy Code code as follows:
1ALTER PROCEDURE [dbo]. [Proc_listpage]
2 (
3 @tblName nvarchar,----the connection to the table or tables to display
4 @fldName nvarchar = ' * ',----list of fields to display
5 @pageSize int = Ten,----the number of records displayed per page
6 @page int = 1,----to display the record for that page
7 @fldSort nvarchar = null,----sort field list or condition
8 @Sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the order of the last sorted field (the last sort field is not ordered)--Program reference: ' Sorta ASC,SORTB Des C,SORTC ')
9 @strCondition nvarchar (1000) = NULL,----query criteria, no where
@ID nvarchar,----primary key for the primary table
One @Dist bit = 0,----whether to add a query field DISTINCT default 0 does not add/1 add
@pageCount int = 1 OUTPUT,----Total number of pages after the query results are paginated
@Counts int = 1 output----The number of records queried
14)
As
SET NOCOUNT on
Declare @sqlTmp nvarchar (1000)----store dynamically generated SQL statements
Declare @strTmp nvarchar (1000)----A query that holds the total number of query results
Declare @strID nvarchar (1000)----A query that holds the ID at the beginning or end of a query
20
Declare @strSortType nvarchar (a)----data collation a
Declare @strFSortType nvarchar (a)----data collation B
23
Declare @SqlSelect nvarchar----SQL construction of queries containing distinct
Declare @SqlCounts nvarchar----SQL construction of the total query containing distinct
26
27
If @Dist = 0
Begin
Set @SqlSelect = ' SELECT '
Set @SqlCounts = ' Count (0) '
End
Or else
The Begin
Set @SqlSelect = ' SELECT distinct '
Set @SqlCounts = ' Count (DISTINCT ' + @ID + ') '
Notoginseng End
38
39
=0 if @Sort
The Begin
The Set @strFSortType = ' ASC '
Set @strSortType = ' DESC '
The end
Or else
The Begin
Set @strFSortType = ' DESC '
Set @strSortType = ' ASC '
End
50
51
52
Generate Query Statements----------------
54--Here @strtmp statements to get the number of query results
If @strCondition is null or @strCondition = '--no display condition set
The Begin
Set @sqlTmp = @fldName + ' from ' + @tblName
The Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName
Set @strID = ' from ' + @tblName
The end
Or else
The Begin
The Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
The Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
The Set @strID = ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
The end
67
Total number of query results obtained by---------
The exec sp_executesql @strTmp, N ' @Counts int out ' and @Counts out
DECLARE @tmpCounts int
If @Counts = 0
Set @tmpCounts = 1
Or else
The Set @tmpCounts = @Counts
75
76--Total paging
Set @pageCount = (@tmpCounts + @pageSize-1)/@pageSize
78
79/**//** The current page is greater than the total number of pages to take the last page **/
If @page > @pageCount
Bayi Set @page = @pageCount
82
--/*-----Data Paging 2-------/*
DECLARE @pageIndex INT--Total/page size
DECLARE @lastcount INT--Total% page size
86
Set @pageIndex = @tmpCounts/@pageSize
The set @lastcount = @tmpCounts% @pageSize
If @lastcount > 0
Set @pageIndex = @pageIndex + 1
Or else
The Set @lastcount = @pagesize
93
94--to match the display
SET NOCOUNT OFF
Select @page curpage, @pageSize pageSize, @pageCount countpage, @tmpCounts [ROWCOUNT]
The SET NOCOUNT ON
98
99--//*** Display Paging
If @strCondition is null or @strCondition = '--no display condition set
The Begin
102 if @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
The Begin
=1 if @page
The Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' + @tblName
"+ @fldSort +" + @strFSortType
Or else
108 begin
109 Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID
=0 if @Sort
112 Set @strTmp = @strTmp + ' > (select Max ('
113 Else
114 Set @strTmp = @strTmp + ' < (select min ('
The Set @strTmp = @strTmp + @ID + ') from ("+ @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar (2 0) + ' + @ID + ' from ' + @tblName
116 + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
117 + ' ORDER BY ' + @fldSort + ' + @strFSortType
118 End
119 End
Or else
121
122 begin
123 Set @page = @pageIndex-@page +1--the latter half of data processing
124 if @page <= 1--last page data display
The Set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@lastcount as VARCHAR) + ' + @fldNa Me+ ' from ' + @tblName
126 + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSor Ttype
127 Else
128 begin
129 Set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldNam E+ ' from ' + @tblName
130 + ' where ' + @ID
131 If @Sort =0
132 Set @strTmp = @strTmp + ' < (select min ('
A.
134 Set @strTmp = @strTmp + ' > (select Max ('
135 Set @strTmp = @strTmp + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar) + ' + @i D + ' from ' + @tblName
136 + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
137 + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSor Ttype
138 End
139 End
140
The end of the end
142
143 Else--there are query conditions
144 BEGIN
145 if @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
146 begin
147 If @page =1
148 Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' + @tblName
149 + ' where 1=1 ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
Or else
151 begin
152 Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + "+ @fldName + ' from ' + @tblName
153 + ' where ' + @ID
154 If @Sort =0
The 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--the latter half of data processing
167 if @page <= 1--last page data display
Set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@lastcount as VARCHAR) + ' + @fldNa Me+ ' from ' + @tblName
169 + ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as TEMPTB ' + ' ord Er by ' + @fldSort + ' + @strFSortType
170 Else
171 begin
172 Set @strTmp = @SqlSelect + ' * FROM (' + @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR) + ' + @fldNam E+ ' 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 Varc Har + ' + @ID + ' from ' + @tblName
179 + ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid) '
180 + ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER by ' + @fld Sort + ' + @strFSortType
181 End
The end of the
183
184 End
185
186------Return Query Results-----
187 SET NOCOUNT off
188 EXEC sp_executesql @strTmp
189 Print @strTmp
