分页存储过程(经典)(页 1) - 编程交流 - 新手电脑网 www.110xs.com 打造最好的新手电脑技术交流中心!

syziy 发表于 2008-10-4 21:39

分页存储过程(经典)

参数：
@PK varchar(50),
主键，用来排序的单一字段，空的话，表示没有主键，存储过程将自动创建标识列主键
@Fields varchar(500), 要显示的字段列表(格式如：ID,Code,Name)i2B7MdiaU Q3lQ
@Tables varchar(1000), 要使用的表集合(Org)9p;Jp+Mj-Q
@Where varchar(500), 查询条件(Code like '100')s;K C{4z&N
@OrderBy varchar(100), 排序条件(支持多个排序字段，如：ID,Code desc,Name desc)z7NL|p
@PageIndex int, 当前要显示的页的页索引，索引从1开始，无记录时为0。
@PageSize int, 页大小c(E2F;K`
创建者：Hollis Yao
创建日期：2006-08-06.eCu4G:Pc7qo
备注：
*/
CREATE PROCEDURE [dbo].[listpage]5~)}-Hux
@PK varchar(50)='',
@Fields varchar(500),
@Tables varchar(1000),9gb+qCe f
@Where varchar(500)='',{$uNG+}` _ I
@OrderBy varchar(100),
@PageIndex int,
@PageSize int
AS&F3U9X'jPw},U j
--替换单引号，避免构造SQL出错0vG9i9A BK/tb
set @Fields = replace(@Fields, '''', '''''')
--要执行的SQL，切分为几个字符串，避免出现长度超过4k时的问题
declare @SQL1 varchar(4000)m9J_n8u$^
declare @SQL2 varchar(4000)
if @PageIndex=0,pM5XCmL+D Ce
set @PageIndex = 1
set @SQL1 = ''P$\Z$_6N-K#z
set @SQL2 = ''(^^*H!^+].q1O
if @Where is not null and len(ltrim(rtrim(@Where))) > 0S[@y8G!P t
set @Where = ' where ' + @WhereCxLC*j],y$p7Z
else,FIgyUL F;{ WBp
set @Where = ' where 1=1'
set @SQL1 = @SQL1 + ' declare @TotalCount int' --声明一个变量，总记录数7M0W/Lq5s/^!|/?f
set @SQL1 = @SQL1 + ' declare @PageCount int' --声明一个变量，总页数5`b2wde/v8Z B_
set @SQL1 = @SQL1 + ' declare @PageIndex int' --声明一个变量，页索引U.m(L)|B(S-A]Y
set @SQL1 = @SQL1 + ' declare @StartRow int' --声明一个变量，当前页第一条记录的索引
set @SQL1 = @SQL1 + ' select @TotalCount=count(*) from ' + @Tables + @Where --获取总记录数4hr:w O/o3{$JZ4u
set @SQL1 = @SQL1 + ' if @PageCount <= 0 begin' --如果记录数为0，直接输出空的结果集
set @SQL1 = @SQL1 + ' select ' + @Fields + ' from ' + @Tables + ' where 1<>1'
set @SQL1 = @SQL1 + ' select 0 as PageIndex,0 as PageCount,'
+ convert(varchar, @PageSize) + ' as PageSize,0 as TotalCount'| c;@7S%u_)qo*bM
set @SQL1 = @SQL1 + ' return end'
set @SQL1 = @SQL1 + ' set @PageCount=(@TotalCount+' + convert(varchar, @PageSize)
+ '-1)/' + convert(varchar, @PageSize) --获取总页数
set @SQL1 = @SQL1 + ' set @PageIndex=' + convert(varchar, @PageIndex)
--设置正确的页索引
set @SQL1 = @SQL1 + ' if @PageIndex<0 set @PageIndex=1'0];P X8YeauC%Cp
set @SQL1 = @SQL1 + ' if @PageIndex>@PageCount and @PageCount>0 2@`zK~n
set @PageIndex=@PageCount'
set @SQL1 = @SQL1 + ' set @StartRow=(@PageIndex-1)*' + convert(varchar, @PageSize)8wEDF @VW!r
+ '+1'iLh.{+B'o,|4k
if (charindex(',', @OrderBy)=0 and charindex(@PK, @OrderBy)>0)
begin
--****************************************************************************sFa|;KzTL
--****************不需要创建主键********************************************
--****************************************************************************!\?%Ela)j aOZ
declare @SortDirection varchar(10) --排序方向，>=：升序，<=：倒序
set @SortDirection = '>='
if charindex('desc', @OrderBy) > 0
set @SortDirection = '<='
set @SQL2 = @SQL2 + ' declare @Sort varchar(100)'
--声明一个变量，用来记录当前页第一条记录的排序字段值|{)[ y"_9C/A
set @SQL2 = @SQL2 + ' set rowcount @StartRow' NEahu!ur!|?
--设置返回记录数截止到当前页的第一条
set @SQL2 = @SQL2 + ' select @Sort=' + @PK + ' from ' e,aAop%nwQ$MZ
+ @Tables + @Where + ' order by ' + @OrderBy --获取当前页第一个排序字段值
set @SQL2 = @SQL2 + ' set rowcount ' + convert(varchar, @PageSize) (u/CZ6P(W
--设置返回记录数为页大小B!DG#hl6ol
set @Where = @Where + ' and ' + @PK + @SortDirection + [url]http://www.izuozhu.cn[/url]
set @SQL2 = @SQL2 + ' select ' + @Fields + ' from ' + @Tables
+ @Where + ' order by ' + @OrderBy
--输出最终显示结果c8vF:Cy?
endlTMfU
else
begino R_Z.|D5tME
--需要创建自增长主键;me/W3^%Zg+Z)_
set @SQL2 = @SQL2 + ' declare @EndRow int'
set @SQL2 = @SQL2 + ' set @EndRow=@PageIndex*' + convert(varchar, @PageSize)| dX5w!H.` b
set @SQL2 = @SQL2 + ' set rowcount @EndRow'
set @SQL2 = @SQL2 + ' declare @PKBegin int' --声明一个变量，开始索引
set @SQL2 = @SQL2 + ' declare @PKEnd int' --声明一个变量，结束索引rb E1`X,H"qQAMr^
set @SQL2 = @SQL2 + ' set @PKBegin=@StartRow'
set @SQL2 = @SQL2 + ' set @PKEnd=@EndRow'
--****************************************************************************
--************对特殊字段进行转换，以便可以插入到临时表******************0GkZH^
--****************************************************************************$szkz3u6U8oz5JJO
declare @TempFields varchar(500)Y.^F X^u@1dL7k
set @TempFields=@FieldsgKp%` |Kw O$vw
set @TempFields = replace(@TempFields, ''''' as CheckBox', '')#T,MI4Bk_
set @TempFields = replace(@TempFields, ''''' as DetailButton', '')
set @TempFields = replace(@TempFields, ''''' as Radio', '')
set @TempFields = LTRIM(RTRIM(@TempFields))}g)Oli|T
if left(@TempFields,1)=',' --去除最左边的逗号](Mj*r"H D2Z
set @TempFields = substring(@TempFields, 2, len(@TempFields))0[ ~0~N:qPt1l#_
if right(@TempFields,1)=',' --去除最右边的逗号)dn rj:ySgK
set @TempFields = substring(@TempFields, 1, len(@TempFields)-1)
set @SQL2 = @SQL2 + ' select identity(int,1,1) as PK,' + @TempFields K7_DtOn
+ ' into #tb from ' + @Tables + @Where + ' order by ' + @OrderBy
--****************************************************************************
--********去除字段的表名前缀，当有字段有别名时，只保留字段别名*********
--****************************************************************************
declare @TotalFields varchar(500)
declare @tmp varchar(50)O5|Y(f.`x
declare @i int]4F-X7GLL2h[7m
declare @j int
declare @iLeft int --左括号的个数
declare @iRight int --右括号的个数l%H(nUqihF
set @i = 0
set @j = 0
set @iLeft = 0c&ey;sX7HKV x3r
set @iRight = 0
set @tmp = ''
set @TotalFields = ''

while (len(@Fields)>0)!qi;z2K(k:b3a
beginZxzP0GWe Wa
set @i = charindex(',', @Fields),n+M:m$CCa
--去除字段的表名前缀本篇文章发表于[url]http://www.izuozhu.cn[/url](小新技术网) 2[*Eid9b-_3H:gG
if (@i=0)
begin
--找不到逗号分割，即表示只剩下最后一个字段k7gJfx/[o5l#C9YX~
set @tmp = @Fields
end
else3N+nl\6vZ%t`
beginQF2N Pxn7p%H$x
set @tmp = substring(@Fields, 1, @i)nEe,K6D:R
end
set @j = charindex('.', @tmp)
if (@j>0)
set @tmp = substring(@tmp, @j+1, len(@tmp))
--*******当有字段有别名时，只保留字段别名*********
z?:|$V$a$Qdt
--带括号的情况要单独处理，如Convert(varchar(10), B.EndDate, 120) as EndDate
while (charindex('(', @tmp) > 0)t8] Urm!qa${j
begin
set @iLeft = @iLeft + 1$Ht:m!Z8BlHW/h
set @tmp = substring(@tmp, charindex('(', @tmp)+1, Len(@tmp))
end
while (charindex(')', @tmp) > 0)0KYGW/L
beginc6xO,}lYH
set @iRight = @iRight + 1
set @tmp = substring(@tmp, charindex(')', @tmp)+1, Len(@tmp))
end
--当括号恰好组队的时候，才能进行字段别名的处理
if (@iLeft = @iRight)
begin/H,xPAx` PT6Fc
set @iLeft = 0:|$yW%Hs-k?5Y
set @iRight = 0
--不对这几个特殊字段作处理:CheckBox、DetailButton、Radio$pg ii:i cj`
if (charindex('CheckBox', @tmp) = 0 and charindexr$vXN^J)O~
('DetailButton', @tmp) = 0 and charindex('Radio', @tmp) = 0)L!z2r2OIeP/V
begins'Qi@*{ F
--判断是否有别名
if (charindex('as', @tmp) > 0)--别名的第一种写法，带'as'的格式4SK6v:m$H |eq;o Q
begin9X-gX G-fc-TDa3u%J,x
set @tmp = substring(@tmp, charindex('as', @tmp)+2, len(@tmp))!IH?AT
m3~E{9l}'r2}
end
elsem`r,xkC"o1I&Y9~.s
begin4~?O&M [
if (charindex(' ', @tmp) > 0)--别名的第二种写法，带空格(" ")的格式h4YD_&e$jU\
begin["yjLFK'k.sy(d%`
while(charindex(' ', @tmp) > 0)"w-N[$k sIX$wE
begin\!\%H%XJ{7eq"C+v
set @tmp = substring(@tmp, charindex(' ', @tmp)+1, len(@tmp))
endQ T5AOl,q'TW
end
end[ I#A^o r"_(g4Ua c
end2K`j p$j H*C]2c
set @TotalFields = @TotalFields + @tmp
end
if (@i=0)
set @Fields = '' L n(M5ib5wk"uL{G
else
set @Fields = substring(@Fields, @i+1, len(@Fields))
endzg`#m}5_}E x`
--print @TotalFieldsip{Y4`$j*D
set @SQL2 = @SQL2 + ' select ' + @TotalFields + ' "@mw\9RqrP
from #tb where PK between @PKBegin and @PKEnd order by PK' jG6S!iF~
--输出最终显示结果
set @SQL2 = @SQL2 + ' drop table #tb'
end teQO;K"}{SI

--输出“PageIndex(页索引)、PageCount(页数)、PageSize(页大小)、TotalCount(总记录数)”
set @SQL2 = @SQL2 + ' select @PageIndex as PageIndex,@PageCount as PageCount,'
+ convert(varchar, @PageSize) + ' as PageSize,@TotalCount as TotalCount'm2@s@+~Ds2zb
--print @SQL1 + @SQL2^K5b8grN
--return)Fx,U;l-x1K
exec(@SQL1 + @SQL2)
GO
最终这个存储过程将生成二张表，表1显示记录集，表2显示当前页码、总页数、每页记录数、总记录数。X(NI)S1T

如果是DataSet的话
就用DataSet.Tables[0]
DataSet.Tables[1]来获取咯 'nE,ew)ZZ/Y,P
如果是SqlDataReader的话 #W8|DV W}
Read一次就是第一张表
然后SqlDataReader.NextResult();得到第二张表

页: [1]

新手电脑网's Archiver

分页存储过程(经典)