分页存储过程
上一篇 / 下一篇 2007-06-05 15:59:09 / 个人分类:ASP
h3p|r(e051Testing软件测试网8LP ?#]0D*C
create procedure sp_search51Testing软件测试网N;M?-~2u#gj:N`
v5Y}R$q h@0@tblName varchar(255), -- 表名
3bzsspY-c4ct0Z0'uSM'q u$lL0@strGetFields varchar(1000) = '*', -- 需要返回的列51Testing软件测试网~ar@ZL
51Testing软件测试网f9y6H.r5qYx@fldName varchar(255)='', -- 排序的字段名51Testing软件测试网/ZV6R6]/X\AK
51Testing软件测试网)c Nvj)p-}!['T*d@PageSize int = 10, -- 页尺寸
GEmP?T5W051Testing软件测试网&eru]%t@PageIndex int = 1, -- 页码51Testing软件测试网jLOX \B%}(t
51Testing软件测试网E ^lX~'dp~@doCount bit = 0, -- 返回记录总数, 非 0 值则返回51Testing软件测试网*g&Q0rq5zt
51Testing软件测试网tT2N!SYW.G9a@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
a&|Ij$O be0qH|%h3I)|0@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
-Y7dpO_}P/Q051Testing软件测试网!IXJ4Ixb YMAS51Testing软件测试网Zm[b/Z'_o
}s!j+_'N9@{;N0declare @strSQL varchar(5000) -- 主语句
)@9Y Gnp L051Testing软件测试网o)kL'S{Kdeclare @strTmp varchar(110) -- 临时变量51Testing软件测试网jb5r4W!V9?
51Testing软件测试网6r e6?-ocw/C/|b3Bdeclare @strOrder varchar(400) -- 排序类型
\a})vo-yD051Testing软件测试网4WQ*M'EE%Pl X5elrYsiu051Testing软件测试网Y;O+Y3sz
if @doCount != 0
x"g:l9s$k%]J051Testing软件测试网!}&~A"Vw@Xbegin51Testing软件测试网/O3D;~1TZ/W.R
51Testing软件测试网b!v0B;K,y3p Ubm!yB
if @strWhere !=''
9@e8{
zEa:W f]0
"@Oj7U I/W wz0 set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
!QA(YE P9E}8H&DB0
3kO&cP[0 else51Testing软件测试网|U{CC:}
51Testing软件测试网"e!p E#d;r:wg:_
set @strSQL = "select count(*) as Total from [" + @tblName + "]"51Testing软件测试网$fS2\nCrD
kj Lg6uE0end
-KT)Y/z3|N.Kt051Testing软件测试网2U!O-PU#rU,R--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况
;M*tPw7Z$J+Z0#Y m^HW mW0else51Testing软件测试网&L1m4`h2_5ZJu
r,J4xBB\#g)k|0begin51Testing软件测试网p3r.@O d
d9g|%UhCHP0
@k:kuJi7h)F9[051Testing软件测试网f!Z%Z}$Ux'Q4X\if @OrderType != 0
$LLO T9X@3Y0q*Q&F#N&M;h b$~x(|0 begin
e_ SaxIN051Testing软件测试网wg f@xaQa.\._yset @strTmp = "<(select min"51Testing软件测试网(zA2U3xX;G
51Testing软件测试网7A3V'N%~f*XU7|set @strOrder = " order by [" + @fldName +"] desc"
.y3RCy-hY4]J H07}%b^|]@.w)CW)}r0 --如果@OrderType不是0,就执行降序,这句很重要!51Testing软件测试网x:ACTB
HdsYX2Q0 end
o9K"v]9xM%}0H9_qyjW/B5? k0 else
*pa)nF;kbKw'r {051Testing软件测试网7tcN!pW9j1S8oL'Nbegin51Testing软件测试网(cxy~D YSim
51Testing软件测试网)AS3Yyp"w;omset @strTmp = ">(select max"
ee$E6Ps({xA051Testing软件测试网9qQ"ok*OHY|set @strOrder = " order by [" + @fldName +"] asc"
K(x&E |1s!wk"[051Testing软件测试网"lj)` nS:TUend
.HV_6M3Ugr#oQu.{051Testing软件测试网UtK8G!tyg51Testing软件测试网.YS"TL4}-pI
|;F9A(w'e0if @PageIndex = 151Testing软件测试网&D2I9JO-e^
51Testing软件测试网 N @X[s^H;jbegin51Testing软件测试网y5@IW$GZm7|Ya
51Testing软件测试网}_$s8H2|6MU5Pif @strWhere != ''
'G5Kr T+h8O8_051Testing软件测试网/d^3uo$Up+{set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
pF/y],j/s |n051Testing软件测试网FOV$k*{'Gelse51Testing软件测试网!SM$ZVga5`D
51Testing软件测试网HP8H]_Xset @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
4^jd4s2AN&p#P4k:CVa051Testing软件测试网1V~fD5Ijv--如果是第一页就执行以上代码,这样会加快执行速度
5gz3w!|{k)Z y0I[o jV)U0end
CR%B,t7],X5I+n0%PL Jz+aR4GRDG0else51Testing软件测试网^X \e/P Z
51Testing软件测试网|U!W6\{$s.y;eAbegin51Testing软件测试网_+XMp6Q$?wh
51Testing软件测试网1J+e0~s\4o--以下代码赋予了@strSQL以真正执行的SQL代码
mWJF(U7k)~!hh0G:r8i,P'f0_$H,S T$^0set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["51Testing软件测试网P8bw+WRM"\
51Testing软件测试网%mT(|Y5H j3u4h+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder51Testing软件测试网F*`B)ok|-U
,_D-Aq [-j)_@0 51Testing软件测试网Y$`bM}i
_] Qgj,mwP)H0if @strWhere != ''51Testing软件测试网|yn2O vZ
51Testing软件测试网ll"w!X*@1awtset @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
q%ug5dh[6I,Kv09|`s+?"VJP/z0 + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["51Testing软件测试网quz C:H
51Testing软件测试网 {&^(yabm+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["51Testing软件测试网utQj2^D-}
/y:Y oqv0j0D0 + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
9J:WDNv0X G0v2Y Xt0 + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder51Testing软件测试网C*OHG\-~p#}H-k
C}-N w+T)ND;i%w0end51Testing软件测试网6~&~4wB4Z+`
51Testing软件测试网4~(@!U(l Qend
!m5{aelindg0