??????????????????????????С??????????????????????????±??г????????????1000????????????????е??????? GID??GID????????????????????????????????С????gid??fariqi??title??Σ???????1??10??100??500??1000??1?? 10??25??50???????????????????????????????????????λ??????

??????????У?????????????????洢?????????100????μ????????????????????ε????????á???????????????з??1000????? ???????????????????????????????з??1????????????????????????????????????????д???????????????
?????????????????????????????????д????洢???????????SQL SERVER??洢???????????????SQL??????????Ч????????WEB??洫????SQL???????Ч???????????洢??????????з?????????? ???????洫??????????????????????????????
????-- ?????????????
1 CREATE PROCEDURE pagination3
2
3 @tblName varchar(255)?? -- ????
4
5 @strGetFields varchar(1000) = '*'?? -- ??????????
6
7 @fldName varchar(255)=''?? -- ??????????
8
9 @PageSize int = 10?? -- ????
10
11 @PageIndex int = 1?? -- ???
12
13 @doCount bit = 0?? -- ???????????? ?? 0 ?????
14
15 @OrderType bit = 0?? -- ????????????? ?? 0 ?????
16
17 @strWhere varchar(1500) = '' -- ??????? (???: ????? where)
18
19 AS
20
21 declare @strSQL varchar(5000) -- ?????
22
23 declare @strTmp varchar(110) -- ???????
24
25 declare @strOrder varchar(400) -- ????????
26
27 if @doCount != 0
28
29 begin
30
31 if @strWhere !=''
32
33 set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
34
35 else
36
37 set @strSQL = "select count(*) as Total from [" + @tblName + "]"
38
39 end
????--????????????????@doCount????????????0????????????????μ????д?????@doCount?0?????
1 else
2
3 begin
4
5 if @OrderType != 0
6
7 begin
8
9 set @strTmp = "<(select min"
10
11 set @strOrder = " order by [" + @fldName +"] desc"
12
13 --???@OrderType????0????н????????????
14
15 end
16
17 else
18
19 begin
20
21 set @strTmp = ">(select max"
22
23 set @strOrder = " order by [" + @fldName +"] asc"
24
25 end
26
27 if @PageIndex = 1
28
29 begin
30
31 if @strWhere != ''
32
33 set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
34
35 else
36
37 set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
????--????????????????????????????????
1 end
2
3 else
4
5 begin
6
7 --???′???????@strSQL????????е?SQL????
8
9 set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
10
11 + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
12
13 if @strWhere != ''
14
15 set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
16
17 + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
18
19 + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
20
21 + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
22
23 + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
24
25 end
26
27 end
28
29 exec (@strSQL)
30
31 GO
?????????????洢?????????????洢????????????д?????????
???????????????????￡???????????????????????????????9???????????洢???????????л??3????????????洢???????????????????????????
???????????????????????洢??????????????????????????????????????????????Ч???????????????????????????????????