分页存储过程

)Cc+x&Sk4J0create procedure sp_search51Testing软件测试网B$E6g"E:AV4P:{6cZ

@tblName   varchar(255),       -- 表名51Testing软件测试网A?'H Q${1_5r$z&q }9O

@strGetFields varchar(1000) = '*',  -- 需要返回的列51Testing软件测试网Fv!}B$o`w

&m%V&rS-S?R0@fldName varchar(255)='',      -- 排序的字段名51Testing软件测试网
y lU2l0GgX)Dtxp

\;Y'{ d8g~,M0@PageSize   int = 10,          -- 页尺寸51Testing软件测试网3nY lTO

J V4x%\?0a4}4z7J T0@PageIndex  int = 1,           -- 页码51Testing软件测试网k3Nm,G4d5b

9?Z SCE m1\0@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回51Testing软件测试网RJ!_4]C|j
}3[K

MSF5P'Ui7^1K0@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序51Testing软件测试网6WX(l.S0n8N3e.~kl

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

Kj:F|q0AS51Testing软件测试网%pNH.L(uq

)\:@ZK P*d0declare @strSQL   varchar(5000)       -- 主语句

Lf!y^h7iP,zb0declare @strTmp   varchar(110)        -- 临时变量51Testing软件测试网 Kx9GUb5S5v

declare @strOrder varchar(400)        -- 排序类型51Testing软件测试网^2?@&L0Y(e$v4b-R

 51Testing软件测试网j+C q^
w8s T6z

if @doCount != 051Testing软件测试网0E.frn&V/{.s|7H

)MdPpI5m8}0begin51Testing软件测试网
B5`'UXX9uj^#d:vd
 
+mG6V1@ E"E0    if @strWhere !=''
Wkv'| C
D0 
7E.acs6H r:S6t0u0        set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere51Testing软件测试网 |3WIP/QC9R
 51Testing软件测试网a Ya&w4^`,A
    else
&r+V W\cQ!}jB0 51Testing软件测试网,D r1W:w~-{V
        set @strSQL = "select count(*) as Total from [" + @tblName + "]"

'E ~0E8E^5kI}0end 51Testing软件测试网y;c|zkd;i@_}@/i

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况51Testing软件测试网+x~@V4y\

else

begin51Testing软件测试网.eQ;?eOt

    if @OrderType != 0

!seGb sF0LO K6[0    begin

x.j(YNP DK4T&X0        set @strTmp = "<(select min"51Testing软件测试网 Im:z o g(jc^

8h5H+C#A J5p0        set @strOrder = " order by [" + @fldName +"] desc"51Testing软件测试网%]
SY#@_%U,X IW-VY

m'C,pFV0        --如果@OrderType不是0，就执行降序，这句很重要！

{#YekRG0    end51Testing软件测试网)pm$TsS{}6V.H

    else51Testing软件测试网@9t bevo&y0q C

    begin51Testing软件测试网lydrz

        set @strTmp = ">(select max"

        set @strOrder = " order by [" + @fldName +"] asc"

9KB
Tr.i^0Z4k0    end

 51Testing软件测试网7sg,h5l:H R;An O/A

if @PageIndex = 1

begin

3l3A9T.B_'s+]0    if @strWhere != ''  

J e_
U1\0    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder51Testing软件测试网cin1I]_

A W:W0N'n`x0     else

cB(@ eP)o,Ad3a D0     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder51Testing软件测试网 b.ic!N.MEt

'C,dE)MY'M0--如果是第一页就执行以上代码，这样会加快执行速度

end

else

'l7V Xw*Lhu+r0begin51Testing软件测试网FkT4dg:I`o]

--以下代码赋予了@strSQL以真正执行的SQL代码51Testing软件测试网+CM;BE,\B

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["51Testing软件测试网IJd Z+w.K%r

"?:IoZw%]h GQ#uk0    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder51Testing软件测试网y7m#r }!j:?i

Z;{fp.n})e(V0 51Testing软件测试网r:{y2I{ md

B!E8R'rh:}0if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["51Testing软件测试网h*S6Ko$H.|(?R

i^['`:l8zz v0        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["51Testing软件测试网jpL_~C ~fF

1fvK:VmvE$Y0        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["51Testing软件测试网CXb._6g2Xb

.N0_%Q7K}3r&z0        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "51Testing软件测试网/~wM'Z/l$HM#Rvv5f

        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder51Testing软件测试网
n+Zb:y^
PeQ:Msb

-[Fdmd {Z0end51Testing软件测试网(M_@4f6u0j1_i {9Z j

end  

S{!d(d&mjEm0exec (@strSQL)

GO