利用存储过程实现大数据量的分页。
创建人:理想国(http://www.utoper.com)--welsham;来源技术:Jax--理想国开发的技术框架
还没有进行详细测试,一般测试的结果:普通机子,百万数据量,不会超过5秒;而网上的那存储过程分页,一般要10秒。
用pagingSQL.sql生成数据结构;在pagingSQL最后将添加一些原始数据,可设置@i的最大值,以决定添加的数据量,时间足够和硬盘足够,可设100万以上
执行paging.asp可查看结果。
http://dl2.csdn.net/down4/20070911/11110206823.rar
page.asp
<
%@LANGUAGE
=
"
VBSCRIPT
"
CODEPAGE
=
"
65001
"
%
>
< %
' ******************************************************************
' ** Jax的大数据量分页
' ** 创建人:理想国(http://www.utoper.com)--welsham;来源技术:Jax--理想国开发的技术框架
' ** 数据表Uto_bkDoc,字段:docID(主键ID)、subject(标题)、content(内容),creator(创建人),creatorID(创建人ID),inTime(创建时间)
' ** 用pagingSQL.sql生成数据结构;在pagingSQL最后将添加一些原始数据,可设置@i的最大值,以决定添加的数据量,时间足够和硬盘足够,可设100万以上
' ** 请把改进结果和测试结果发给我们:Utoper@163.com 或 welsham@163.com
' ******************************************************************
Dim rs,conn,connStr
Dim pageSize,page,recordCount,query,order
Dim time1
time1 = timer ()
' 数据库配置
connStr = " Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=localhost;uid=test;pwd=test2006;database=ASPNETMisDB; "
' 初始化conn和rs
Call initConn(conn)
Call initRs(rs)
' 演示:生成SQL及调用分页,建立rs
pageSize = Request.QueryString( " pageSize " )
page = Request.QueryString( " page " )
Call getData(pageSize,page,query,order)
' 调用视力,显示数据
Call view()
' 清除conn和rs
Call clearConn(conn)
Call clearRs(rs)
Function getData(pageSize,page,query,order)
Dim sqls( 11 ),orders,UB,i
Dim top,docID,kw,creator,creatorID
top = getQuery(query, " top " )
If top = "" then top = " 0 "
docID = getQuery(query, " docID " )
kw = Trim (getQuery(query, " kw " ))
creator = getQuery(query, " creator " )
creatorID = getQuery(query, " creatorID " )
' 这是Jax的安全检测,防止SQL注入
' If Not oDoSafe.pFilter(Array(top,docID,kw,creator,creatorID),Array(12,21,21,21,12)) then Exit Function
sqls( 0 ) = CLng (top)
sqls( 1 ) = " d.docID,d.subject,d.creator,d.creatorID,d.inTime "
sqls( 2 ) = " Uto_bkDoc As d "
sqls( 3 ) = ""
If docID <> "" then sqls( 3 ) = sqls( 3 ) & " and d.docID in( " & docID & " ) "
If kw <> "" then sqls( 3 ) = sqls( 3 ) & " and d.kw Like '% " & kw & " ' "
If creator <> "" then sqls( 3 ) = sqls( 3 ) & " and d.creator Like '% " & creator & " %' "
If creatorID <> "" then sqls( 3 ) = sqls( 3 ) & " and d.creatorID= " & creatorID
If sqls( 3 ) <> "" then sqls( 3 ) = " Where " & Mid (sqls( 3 ), 5 )
sqls( 4 ) = ""
If order = "" then order = " inTime_ASC "
orders = Split (order, " - " )
UB = UBound (orders)
sqls( 5 ) = ""
For i = 0 to UB
orders(i) = Split (orders(i), " _ " )
Select Case orders(i)( 0 )
Case " inTime "
sqls( 5 ) = sqls( 5 ) & " ,d. " & orders(i)( 0 ) & " " & orders(i)( 1 )
End Select
Next
sqls( 5 ) = " Order By " & Mid (sqls( 5 ), 2 )
sqls( 6 ) = " d.docID "
sqls( 7 ) = " 1 "
sqls( 8 ) = ""
sqls( 9 ) = ""
If pageSize = "" then pageSize = 25
If page = "" then page = 1
sqls( 10 ) = CLng (pageSize)
sqls( 11 ) = CLng (page)
' 调用
getData = getList(conn,rs,sqls)
End Function
' 数据库操作
Sub initConn(conn)
Set conn = Server.CreateObject( " ADODB.Connection " )
conn.Mode = 3
conn.open connStr
End Sub
Sub clearConn(conn)
conn.Close
Set conn = nothing
End Sub
Sub initRs(rs)
Set rs = Server.CreateObject( " ADODB.RecordSet " )
End Sub
Sub clearRs(rs)
Set rs = nothing
End Sub
Function getQuery(ByVal qUrl,qName)
Dim qStart,qEnd
If qUrl <> "" then
qUrl = " & " & qUrl
qStart = InStr (qUrl, " & " & qName & " = " )
If qStart > 0 then
qStart = qStart + Len (qName) + 2
qEnd = InStr (qStart,qUrl, " & " )
If qEnd >= qStart then
getQuery = Mid (qUrl,qStart,qEnd - qStart)
else
getQuery = Mid (qUrl,qStart)
End if
else
getQuery = ""
End if
else
getQuery = ""
End if
End Function
' 分页调用
Function getList(conn,rs,sqls)
' 参数转化,预防错误
sqls( 10 ) = CLng (sqls( 10 ))
sqls( 11 ) = CLng (sqls( 11 ))
' 调用分页存储过程
set cmd = Server.CreateObject( " ADODB.Command " )
With cmd
.ActiveConnection = conn
.CommandType = 4
.CommandText = " Uto_paging "
.Parameters.Append(cmd.CreateParameter( " @pTop " , 3 , 1 ,,sqls( 0 ))) ' select top的条数,0时表示所有
.Parameters.Append(cmd.CreateParameter( " @pField " , 200 , 1 , 300 ,sqls( 1 ))) ' 字段
.Parameters.Append(cmd.CreateParameter( " @pTable " , 200 , 1 , 700 ,sqls( 2 ))) ' 表
.Parameters.Append(cmd.CreateParameter( " @pWhere " , 200 , 1 , 650 ,sqls( 3 ))) ' where
.Parameters.Append(cmd.CreateParameter( " @pWhere2 " , 200 , 1 , 650 ,sqls( 4 ))) ' where2,二次查询筛选
.Parameters.Append(cmd.CreateParameter( " @pOrder " , 200 , 1 , 100 ,sqls( 5 ))) ' order等
.Parameters.Append(cmd.CreateParameter( " @pID " , 200 , 1 , 25 ,sqls( 6 ))) ' 主键
.Parameters.Append(cmd.CreateParameter( " @pIDType " , 200 , 1 , 1 ,sqls( 7 ))) ' 主键类型,1为int,2为char
.Parameters.Append(cmd.CreateParameter( " @pStart " , 200 , 1 , 300 ,sqls( 8 ))) ' 头部
.Parameters.Append(cmd.CreateParameter( " @pEnd " , 200 , 1 , 1000 ,sqls( 9 ))) ' 尾部
.Parameters.Append(cmd.CreateParameter( " @page_size " , 3 , 1 ,,sqls( 10 ))) ' 页大小
.Parameters.Append(cmd.CreateParameter( " @page_no " , 3 , 1 ,,sqls( 11 ))) ' 页码
.Parameters.Append(cmd.CreateParameter( " @RecordCount " , 3 , 2 )) ' 返回的记录数
.Execute()
getList = cmd.Parameters( " @RecordCount " )
set rs = cmd.Execute()
End With
recordCount = getList
Set cmd = nothing
End Function
Sub view()
% >
< !DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
< meta http - equiv = " Content-Type " content = " text/html; charset=utf-8 " />
< title > Jax的大数据量分页 </ title >
</ head >
< body >
< p > Jax的大数据量分页: </ p >
< table width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 1 " bgcolor = " #CCCCCC " >
< tr >
< td width = " 15% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > ID </ td >
< td width = " 23% " height = " 25 " bgcolor = " #EFEFEF " > 标题 </ td >
< td width = " 23% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > 创建人 </ td >
< td width = " 39% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > 创建时间 </ td >
</ tr >
< %
If recordCount = 0 then
% >
< tr >
< td height = " 25 " colspan = " 4 " align = " center " bgcolor = " #FFFFFF " > 没有找到记录 </ td >
</ tr >
< %
else
Do While Not rs.EOF
% >
< tr >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " docID " ) % ></ td >
< td height = " 25 " bgcolor = " #FFFFFF " >< % = rs( " subject " ) % ></ td >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " creator " ) % ></ td >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " inTime " ) % ></ td >
</ tr >
< %
rs.MoveNext
loop
rs.Close
% >
< tr >
< td height = " 25 " colspan = " 4 " align = " center " bgcolor = " #EFEFEF " > 共有 < % = recordCount % > 条记录,当前第 < % = page % > 页,每页显示 < % = pageSize % > 条 </ td >
</ tr >
< %
End if
% >
</ table >
< p > 运行时间: < % = ( timer () - time1) * 1000 % > ms </ p >
</ body >
</ html >
< %
End Sub
% >
< %
' ******************************************************************
' ** Jax的大数据量分页
' ** 创建人:理想国(http://www.utoper.com)--welsham;来源技术:Jax--理想国开发的技术框架
' ** 数据表Uto_bkDoc,字段:docID(主键ID)、subject(标题)、content(内容),creator(创建人),creatorID(创建人ID),inTime(创建时间)
' ** 用pagingSQL.sql生成数据结构;在pagingSQL最后将添加一些原始数据,可设置@i的最大值,以决定添加的数据量,时间足够和硬盘足够,可设100万以上
' ** 请把改进结果和测试结果发给我们:Utoper@163.com 或 welsham@163.com
' ******************************************************************
Dim rs,conn,connStr
Dim pageSize,page,recordCount,query,order
Dim time1
time1 = timer ()
' 数据库配置
connStr = " Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=localhost;uid=test;pwd=test2006;database=ASPNETMisDB; "
' 初始化conn和rs
Call initConn(conn)
Call initRs(rs)
' 演示:生成SQL及调用分页,建立rs
pageSize = Request.QueryString( " pageSize " )
page = Request.QueryString( " page " )
Call getData(pageSize,page,query,order)
' 调用视力,显示数据
Call view()
' 清除conn和rs
Call clearConn(conn)
Call clearRs(rs)
Function getData(pageSize,page,query,order)
Dim sqls( 11 ),orders,UB,i
Dim top,docID,kw,creator,creatorID
top = getQuery(query, " top " )
If top = "" then top = " 0 "
docID = getQuery(query, " docID " )
kw = Trim (getQuery(query, " kw " ))
creator = getQuery(query, " creator " )
creatorID = getQuery(query, " creatorID " )
' 这是Jax的安全检测,防止SQL注入
' If Not oDoSafe.pFilter(Array(top,docID,kw,creator,creatorID),Array(12,21,21,21,12)) then Exit Function
sqls( 0 ) = CLng (top)
sqls( 1 ) = " d.docID,d.subject,d.creator,d.creatorID,d.inTime "
sqls( 2 ) = " Uto_bkDoc As d "
sqls( 3 ) = ""
If docID <> "" then sqls( 3 ) = sqls( 3 ) & " and d.docID in( " & docID & " ) "
If kw <> "" then sqls( 3 ) = sqls( 3 ) & " and d.kw Like '% " & kw & " ' "
If creator <> "" then sqls( 3 ) = sqls( 3 ) & " and d.creator Like '% " & creator & " %' "
If creatorID <> "" then sqls( 3 ) = sqls( 3 ) & " and d.creatorID= " & creatorID
If sqls( 3 ) <> "" then sqls( 3 ) = " Where " & Mid (sqls( 3 ), 5 )
sqls( 4 ) = ""
If order = "" then order = " inTime_ASC "
orders = Split (order, " - " )
UB = UBound (orders)
sqls( 5 ) = ""
For i = 0 to UB
orders(i) = Split (orders(i), " _ " )
Select Case orders(i)( 0 )
Case " inTime "
sqls( 5 ) = sqls( 5 ) & " ,d. " & orders(i)( 0 ) & " " & orders(i)( 1 )
End Select
Next
sqls( 5 ) = " Order By " & Mid (sqls( 5 ), 2 )
sqls( 6 ) = " d.docID "
sqls( 7 ) = " 1 "
sqls( 8 ) = ""
sqls( 9 ) = ""
If pageSize = "" then pageSize = 25
If page = "" then page = 1
sqls( 10 ) = CLng (pageSize)
sqls( 11 ) = CLng (page)
' 调用
getData = getList(conn,rs,sqls)
End Function
' 数据库操作
Sub initConn(conn)
Set conn = Server.CreateObject( " ADODB.Connection " )
conn.Mode = 3
conn.open connStr
End Sub
Sub clearConn(conn)
conn.Close
Set conn = nothing
End Sub
Sub initRs(rs)
Set rs = Server.CreateObject( " ADODB.RecordSet " )
End Sub
Sub clearRs(rs)
Set rs = nothing
End Sub
Function getQuery(ByVal qUrl,qName)
Dim qStart,qEnd
If qUrl <> "" then
qUrl = " & " & qUrl
qStart = InStr (qUrl, " & " & qName & " = " )
If qStart > 0 then
qStart = qStart + Len (qName) + 2
qEnd = InStr (qStart,qUrl, " & " )
If qEnd >= qStart then
getQuery = Mid (qUrl,qStart,qEnd - qStart)
else
getQuery = Mid (qUrl,qStart)
End if
else
getQuery = ""
End if
else
getQuery = ""
End if
End Function
' 分页调用
Function getList(conn,rs,sqls)
' 参数转化,预防错误
sqls( 10 ) = CLng (sqls( 10 ))
sqls( 11 ) = CLng (sqls( 11 ))
' 调用分页存储过程
set cmd = Server.CreateObject( " ADODB.Command " )
With cmd
.ActiveConnection = conn
.CommandType = 4
.CommandText = " Uto_paging "
.Parameters.Append(cmd.CreateParameter( " @pTop " , 3 , 1 ,,sqls( 0 ))) ' select top的条数,0时表示所有
.Parameters.Append(cmd.CreateParameter( " @pField " , 200 , 1 , 300 ,sqls( 1 ))) ' 字段
.Parameters.Append(cmd.CreateParameter( " @pTable " , 200 , 1 , 700 ,sqls( 2 ))) ' 表
.Parameters.Append(cmd.CreateParameter( " @pWhere " , 200 , 1 , 650 ,sqls( 3 ))) ' where
.Parameters.Append(cmd.CreateParameter( " @pWhere2 " , 200 , 1 , 650 ,sqls( 4 ))) ' where2,二次查询筛选
.Parameters.Append(cmd.CreateParameter( " @pOrder " , 200 , 1 , 100 ,sqls( 5 ))) ' order等
.Parameters.Append(cmd.CreateParameter( " @pID " , 200 , 1 , 25 ,sqls( 6 ))) ' 主键
.Parameters.Append(cmd.CreateParameter( " @pIDType " , 200 , 1 , 1 ,sqls( 7 ))) ' 主键类型,1为int,2为char
.Parameters.Append(cmd.CreateParameter( " @pStart " , 200 , 1 , 300 ,sqls( 8 ))) ' 头部
.Parameters.Append(cmd.CreateParameter( " @pEnd " , 200 , 1 , 1000 ,sqls( 9 ))) ' 尾部
.Parameters.Append(cmd.CreateParameter( " @page_size " , 3 , 1 ,,sqls( 10 ))) ' 页大小
.Parameters.Append(cmd.CreateParameter( " @page_no " , 3 , 1 ,,sqls( 11 ))) ' 页码
.Parameters.Append(cmd.CreateParameter( " @RecordCount " , 3 , 2 )) ' 返回的记录数
.Execute()
getList = cmd.Parameters( " @RecordCount " )
set rs = cmd.Execute()
End With
recordCount = getList
Set cmd = nothing
End Function
Sub view()
% >
< !DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
< meta http - equiv = " Content-Type " content = " text/html; charset=utf-8 " />
< title > Jax的大数据量分页 </ title >
</ head >
< body >
< p > Jax的大数据量分页: </ p >
< table width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 1 " bgcolor = " #CCCCCC " >
< tr >
< td width = " 15% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > ID </ td >
< td width = " 23% " height = " 25 " bgcolor = " #EFEFEF " > 标题 </ td >
< td width = " 23% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > 创建人 </ td >
< td width = " 39% " height = " 25 " align = " center " bgcolor = " #EFEFEF " > 创建时间 </ td >
</ tr >
< %
If recordCount = 0 then
% >
< tr >
< td height = " 25 " colspan = " 4 " align = " center " bgcolor = " #FFFFFF " > 没有找到记录 </ td >
</ tr >
< %
else
Do While Not rs.EOF
% >
< tr >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " docID " ) % ></ td >
< td height = " 25 " bgcolor = " #FFFFFF " >< % = rs( " subject " ) % ></ td >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " creator " ) % ></ td >
< td height = " 25 " align = " center " bgcolor = " #FFFFFF " >< % = rs( " inTime " ) % ></ td >
</ tr >
< %
rs.MoveNext
loop
rs.Close
% >
< tr >
< td height = " 25 " colspan = " 4 " align = " center " bgcolor = " #EFEFEF " > 共有 < % = recordCount % > 条记录,当前第 < % = page % > 页,每页显示 < % = pageSize % > 条 </ td >
</ tr >
< %
End if
% >
</ table >
< p > 运行时间: < % = ( timer () - time1) * 1000 % > ms </ p >
</ body >
</ html >
< %
End Sub
% >
pagingSQL.sql
CREATE
TABLE
[
Uto_bkDoc
]
(
[ docID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ subject ] [ nvarchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ content ] [ ntext ] COLLATE Chinese_PRC_CI_AS NULL ,
[ creator ] [ nvarchar ] ( 20 ) NULL ,
[ creatorID ] [ int ] NULL ,
[ inTime ] [ datetime ] NULL ,
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
ALTER TABLE [ Uto_bkDoc ] ADD
CONSTRAINT [ DF_Uto_bkDoc_inTime ] DEFAULT ( getdate ()) FOR [ inTime ] ,
CONSTRAINT [ PK_UTO_BKDOC ] PRIMARY KEY NONCLUSTERED
(
[ docID ]
) ON [ PRIMARY ]
GO
CREATE PROCEDURE Uto_paging
@pTop int , @pField nvarchar ( 300 ), @pTable nvarchar ( 700 ), @pWhere nvarchar ( 650 ), @pWhere2 nvarchar ( 650 ), @pOrder nvarchar ( 100 ), @pID nvarchar ( 25 ), @pIDType nvarchar ( 1 )
, @pStart nvarchar ( 300 ), @pEnd nvarchar ( 1000 ), @page_size int , @page_no int , @RecordCount int output
AS
Begin
Declare @sqlstr nvarchar ( 4000 ), @IDStr nvarchar ( 2200 )
Declare @lower int , @upper int , @upper1 int
Set @lower = ( @page_no - 1 ) * @page_size
Set @upper = @lower + @page_size
Set @upper1 = @upper + 1
Set nocount on
Set @sqlstr = N ' Set @i=0 ' + char ( 13 )
Set @sqlstr = @sqlstr + N ' Set @IDStr=N '''' ' + char ( 13 )
Set @sqlstr = @sqlstr + N ' Select '
IF @pTop > 0
Set @sqlstr = @sqlstr + N ' Top ' + Cast ( @pTop As nvarchar ( 10 )) + N ' '
Set @sqlstr = @sqlstr + N ' @i=@i+1,@IDStr=Case when @i> ' + Cast ( @upper As nvarchar ( 10 )) + N ' then @IDStr '
Set @sqlstr = @sqlstr + N ' when @i> ' + Cast ( @lower As nvarchar ( 10 )) + N ' and @i< ' + Cast ( @upper1 As nvarchar ( 10 )) + N ' then @IDStr+ '
IF @pIDType = ' 1 '
Set @sqlstr = @sqlstr + N ' Cast( ' + @pID + N ' As nvarchar(10))+ '' , '' '
IF @pIDType = ' 2 '
Set @sqlstr = @sqlstr + @pID + N ' + '' , '' '
Set @sqlstr = @sqlstr + N ' else N '''' End '
Set @sqlstr = @sqlstr + N ' From ' + @pTable + N ' ' + @pWhere + N ' ' + @pOrder
EXECUTE sp_executesql @sqlstr ,N ' @i int output,@IDStr nvarchar(2200) output ' , @RecordCount output, @IDStr output
Set nocount off
IF Len ( @IDStr ) > 0
Begin
Set @IDStr = Left ( @IDStr , Len ( @IDStr ) - 1 )
IF @pIDType = ' 2 '
Set @IDStr = '''' + Replace ( @IDStr , ' , ' , ''' , ''' ) + ''''
Set @sqlstr = ' Select ' + @pField + ' From ' + @pTable + ' Where ' + @pID + ' in ( ' + @IDStr + ' ) '
IF Len ( @pWhere2 ) > 0
Set @sqlstr = @sqlstr + ' and ' + @pWhere2
IF Len ( @pStart ) > 0
Set @sqlstr = @pStart + @sqlstr
IF Len ( @pEnd ) > 0
Set @sqlstr = @sqlstr + @pEnd
Else
Set @sqlstr = @sqlstr + ' ' + @pOrder
End
else
Begin
Set @sqlstr = ' Select ' + @pField + ' From ' + @pTable + ' Where ' + @pID + ' = '
IF @pIDType = ' 1 '
Set @sqlstr = @sqlstr + ' -1 '
Else
Set @sqlstr = @sqlstr + ''''''
End
Exec ( @sqlstr )
End
GO
/**/ /*
添加原始数据,可修改@i<3000的值,以决定测试的数据量
*/
Declare @i int
Set @i = 0
While @i < 100000
BEGIN
Insert Into Uto_bkDoc(subject,creator) Values ( ' 题 ' + Cast ( @i As varchar ( 10 )), ' system ' )
Set @i = @i + 1
END
[ docID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ subject ] [ nvarchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ content ] [ ntext ] COLLATE Chinese_PRC_CI_AS NULL ,
[ creator ] [ nvarchar ] ( 20 ) NULL ,
[ creatorID ] [ int ] NULL ,
[ inTime ] [ datetime ] NULL ,
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
ALTER TABLE [ Uto_bkDoc ] ADD
CONSTRAINT [ DF_Uto_bkDoc_inTime ] DEFAULT ( getdate ()) FOR [ inTime ] ,
CONSTRAINT [ PK_UTO_BKDOC ] PRIMARY KEY NONCLUSTERED
(
[ docID ]
) ON [ PRIMARY ]
GO
CREATE PROCEDURE Uto_paging
@pTop int , @pField nvarchar ( 300 ), @pTable nvarchar ( 700 ), @pWhere nvarchar ( 650 ), @pWhere2 nvarchar ( 650 ), @pOrder nvarchar ( 100 ), @pID nvarchar ( 25 ), @pIDType nvarchar ( 1 )
, @pStart nvarchar ( 300 ), @pEnd nvarchar ( 1000 ), @page_size int , @page_no int , @RecordCount int output
AS
Begin
Declare @sqlstr nvarchar ( 4000 ), @IDStr nvarchar ( 2200 )
Declare @lower int , @upper int , @upper1 int
Set @lower = ( @page_no - 1 ) * @page_size
Set @upper = @lower + @page_size
Set @upper1 = @upper + 1
Set nocount on
Set @sqlstr = N ' Set @i=0 ' + char ( 13 )
Set @sqlstr = @sqlstr + N ' Set @IDStr=N '''' ' + char ( 13 )
Set @sqlstr = @sqlstr + N ' Select '
IF @pTop > 0
Set @sqlstr = @sqlstr + N ' Top ' + Cast ( @pTop As nvarchar ( 10 )) + N ' '
Set @sqlstr = @sqlstr + N ' @i=@i+1,@IDStr=Case when @i> ' + Cast ( @upper As nvarchar ( 10 )) + N ' then @IDStr '
Set @sqlstr = @sqlstr + N ' when @i> ' + Cast ( @lower As nvarchar ( 10 )) + N ' and @i< ' + Cast ( @upper1 As nvarchar ( 10 )) + N ' then @IDStr+ '
IF @pIDType = ' 1 '
Set @sqlstr = @sqlstr + N ' Cast( ' + @pID + N ' As nvarchar(10))+ '' , '' '
IF @pIDType = ' 2 '
Set @sqlstr = @sqlstr + @pID + N ' + '' , '' '
Set @sqlstr = @sqlstr + N ' else N '''' End '
Set @sqlstr = @sqlstr + N ' From ' + @pTable + N ' ' + @pWhere + N ' ' + @pOrder
EXECUTE sp_executesql @sqlstr ,N ' @i int output,@IDStr nvarchar(2200) output ' , @RecordCount output, @IDStr output
Set nocount off
IF Len ( @IDStr ) > 0
Begin
Set @IDStr = Left ( @IDStr , Len ( @IDStr ) - 1 )
IF @pIDType = ' 2 '
Set @IDStr = '''' + Replace ( @IDStr , ' , ' , ''' , ''' ) + ''''
Set @sqlstr = ' Select ' + @pField + ' From ' + @pTable + ' Where ' + @pID + ' in ( ' + @IDStr + ' ) '
IF Len ( @pWhere2 ) > 0
Set @sqlstr = @sqlstr + ' and ' + @pWhere2
IF Len ( @pStart ) > 0
Set @sqlstr = @pStart + @sqlstr
IF Len ( @pEnd ) > 0
Set @sqlstr = @sqlstr + @pEnd
Else
Set @sqlstr = @sqlstr + ' ' + @pOrder
End
else
Begin
Set @sqlstr = ' Select ' + @pField + ' From ' + @pTable + ' Where ' + @pID + ' = '
IF @pIDType = ' 1 '
Set @sqlstr = @sqlstr + ' -1 '
Else
Set @sqlstr = @sqlstr + ''''''
End
Exec ( @sqlstr )
End
GO
/**/ /*
添加原始数据,可修改@i<3000的值,以决定测试的数据量
*/
Declare @i int
Set @i = 0
While @i < 100000
BEGIN
Insert Into Uto_bkDoc(subject,creator) Values ( ' 题 ' + Cast ( @i As varchar ( 10 )), ' system ' )
Set @i = @i + 1
END