分页存储过程

Create PROC P_viewPage
/**/ /*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableName VARCHAR ( 200 ), -- 表名
@FieldList VARCHAR ( 2000 ), -- 显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR ( 100 ), -- 单一主键或唯一值键
@Where VARCHAR ( 2000 ), -- 查询条件 不含'where'字符，如id>10 and len(userid)>9
@Order VARCHAR ( 1000 ), -- 排序 不含'order by'字符，如id asc,userid desc，必须指定asc或 desc
-- 注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT , -- 排序规则 1:主键/唯一值正序asc 2:主键/唯一值倒序desc 3:非主键或多列排序方法
@RecorderCount INT , -- 记录总数 0:会返回总记录
@PageSize INT , -- 每页输出的记录数
@PageIndex INT , -- 当前页数
@TotalCount INT OUTPUT , -- 记返回总记录
@TotalPageCount INT OUTPUT -- 返回总页数
AS
SET NOCOUNT ON
IF ISNULL ( @TotalCount , '' ) = '' SET @TotalCount = 0
SET @Order = RTRIM ( LTRIM ( @Order ))
SET @PrimaryKey = RTRIM ( LTRIM ( @PrimaryKey ))
SET @FieldList = REPLACE ( RTRIM ( LTRIM ( @FieldList )), ' ' , '' )
WHILE CHARINDEX ( ' , ' , @Order ) > 0 or CHARINDEX ( ' , ' , @Order ) > 0
BEGIN
SET @Order = REPLACE ( @Order , ' , ' , ' , ' )
SET @Order = REPLACE ( @Order , ' , ' , ' , ' )
END
IF ISNULL ( @TableName , '' ) = '' or ISNULL ( @FieldList , '' ) = ''
or ISNULL ( @PrimaryKey , '' ) = ''
or @SortType < 1 or @SortType > 3
or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0
BEGIN
PRINT ( ' ERR_00 ' )
RETURN
END
IF @SortType = 3
BEGIN
IF ( UPPER ( RIGHT ( @Order , 4 )) != ' ASC ' AND UPPER ( RIGHT ( @Order , 5 )) != ' DESC ' )
BEGIN PRINT ( ' ERR_02 ' ) RETURN END
END
DECLARE @new_where1 VARCHAR ( 1000 )
DECLARE @new_where2 VARCHAR ( 1000 )
DECLARE @new_order1 VARCHAR ( 1000 )
DECLARE @new_order2 VARCHAR ( 1000 )
DECLARE @new_order3 VARCHAR ( 1000 )
DECLARE @Sql VARCHAR ( 8000 )
DECLARE @SqlCount NVARCHAR ( 4000 )
IF ISNULL ( @where , '' ) = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' Where '
END
ELSE
BEGIN
SET @new_where1 = ' Where ' + @where
SET @new_where2 = ' Where ' + @where + ' AND '
END
IF ISNULL ( @order , '' ) = '' or @SortType = 1 or @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC '
SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC '
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC '
SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC '
END
END
ELSE
BEGIN
SET @new_order1 = ' orDER BY ' + @Order
END

IF @SortType = 3 AND CHARINDEX ( ' , ' + @PrimaryKey + ' ' , ' , ' + @Order ) > 0
BEGIN
SET @new_order1 = ' orDER BY ' + @Order
SET @new_order2 = @Order + ' , '
SET @new_order2 = REPLACE ( REPLACE ( @new_order2 , ' ASC, ' , ' {ASC}, ' ), ' DESC, ' , ' {DESC}, ' )
SET @new_order2 = REPLACE ( REPLACE ( @new_order2 , ' {ASC}, ' , ' DESC, ' ), ' {DESC}, ' , ' ASC, ' )
SET @new_order2 = ' orDER BY ' + SUBSTRING ( @new_order2 , 1 , LEN ( @new_order2 ) - 1 )
IF @FieldList <> ' * '
BEGIN
SET @new_order3 = REPLACE ( REPLACE ( @Order + ' , ' , ' ASC, ' , ' , ' ), ' DESC, ' , ' , ' )
SET @FieldList = ' , ' + @FieldList
WHILE CHARINDEX ( ' , ' , @new_order3 ) > 0
BEGIN
IF CHARINDEX ( SUBSTRING ( ' , ' + @new_order3 , 1 , CHARINDEX ( ' , ' , @new_order3 )), ' , ' + @FieldList + ' , ' ) > 0
BEGIN
SET @FieldList =
@FieldList + ' , ' + SUBSTRING ( @new_order3 , 1 , CHARINDEX ( ' , ' , @new_order3 ))
END
SET @new_order3 =
SUBSTRING ( @new_order3 , CHARINDEX ( ' , ' , @new_order3 ) + 1 , LEN ( @new_order3 ))
END
SET @FieldList = SUBSTRING ( @FieldList , 2 , LEN ( @FieldList ))
END
END

SET @SqlCount = ' Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/ '
+ CAST ( @PageSize AS VARCHAR ) + ' ) FROM (Select * FROM ' + @TableName + @new_where1 + ' ) AS T '
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount ,N ' @TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT ' ,
@TotalCount OUTPUT, @TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING (( @TotalCount + 0.0 ) / @PageSize )
BEGIN
SET @PageIndex = CEILING (( @TotalCount + 0.0 ) / @PageSize )
END
IF @PageIndex = 1 or @PageIndex >= CEILING (( @TotalCount + 0.0 ) / @PageSize )
BEGIN
IF @PageIndex = 1 -- 返回第一页数据
BEGIN
SET @Sql = ' Select * FROM (Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order1
END
IF @PageIndex >= CEILING (( @TotalCount + 0.0 ) / @PageSize ) -- 返回最后一页数据
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( ABS ( @PageSize * @PageIndex - @TotalCount - @PageSize ))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE

BEGIN
IF @SortType = 1 -- 仅主键正序排序
BEGIN
IF @PageIndex <= CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2 -- 正向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ ' (Select MAX( ' + @PrimaryKey + ' ) FROM (Select TOP '
+ STR ( @PageSize * ( @PageIndex - 1 )) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 + ' ) AS TMP) ' + @new_order1
END
ELSE -- 反向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @PageSize ) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ ' (Select MIN( ' + @PrimaryKey + ' ) FROM (Select TOP '
+ STR ( @TotalCount - @PageSize * @PageIndex ) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 + ' ) AS TMP) ' + @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 -- 仅主键反序排序
BEGIN
IF @PageIndex <= CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2 -- 正向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ ' (Select MIN( ' + @PrimaryKey + ' ) FROM (Select TOP '
+ STR ( @PageSize * ( @PageIndex - 1 )) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 + ' ) AS TMP) ' + @new_order1
END
ELSE -- 反向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @PageSize ) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ ' (Select MAX( ' + @PrimaryKey + ' ) FROM (Select TOP '
+ STR ( @TotalCount - @PageSize * @PageIndex ) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 + ' ) AS TMP) ' + @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 -- 多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX ( ' , ' + @PrimaryKey + ' ' , ' , ' + @Order ) = 0
BEGIN PRINT ( ' ERR_02 ' ) RETURN END
IF @PageIndex <= CEILING (( @TotalCount + 0.0 ) / @PageSize ) / 2 -- 正向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @PageSize * @PageIndex ) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE -- 反向检索
BEGIN
SET @Sql = ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @PageSize ) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR ( @TotalCount - @PageSize * @PageIndex + @PageSize ) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
PRINT ( @SQL )
EXEC ( @Sql )



-- --------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------
sql测试:
-- 测试数据
-- select count(*) from T_TEST
Create TABLE T_TEST(ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,CLASS_A VARCHAR ( 50 ),CLASS_B VARCHAR ( 50 ),ADD_DT DATETIME )
DECLARE @I INT
Select @I = 1
WHILE @I < = 999998
BEGIN
Insert INTO T_TEST(CLASS_A,CLASS_B,ADD_DT)
Select CASE @I % 2 WHEN 0 THEN ' CLASS_A ' + CAST ( @I AS VARCHAR ) ELSE CAST ( @I AS VARCHAR ) END ,
CASE @I % 5 WHEN 0 THEN ' CLASS_B ' + CAST ( @I AS VARCHAR ) ELSE CAST ( @I AS VARCHAR ) END , GETDATE ()
Select @I = @I + 1
END
Create INDEX _ INDEX ON T_TEST(add_dt,id)
使用： 以多列排序为例
第一页查询，
第一页取出总记录和总页数，以后就不要从数据库里取了
DECLARE @T1 DATETIME , @T2 DATETIME
DECLARE @A INT , @B INT
Select @T1 = GETDATE ()
EXEC P_viewPage ' T_TEST ' , ' ID,ADD_DT ' , ' id ' , '' , ' add_dt asc,id asc ' , 3 , 0 , 10 , 1 , @A out, @B out
Select @T2 = GETDATE ()
Select CAST ( DATEDIFF (ms, @T1 , @T2 ) AS VARCHAR ) + ' ms '
Select @A , @B
其它页(如第1161页)：
DECLARE @T1 DATETIME , @T2 DATETIME
DECLARE @A INT , @B INT
Select @T1 = GETDATE ()
EXEC P_viewPage ' T_TEST ' , ' ID,ADD_DT ' , ' id ' , '' , ' add_dt asc,id asc ' , 3 , 999998 , 10 , 1161 , null , null
Select @T2 = GETDATE ()
Select CAST ( DATEDIFF (ms, @T1 , @T2 ) AS VARCHAR ) + ' ms '
-- Select @A,@B

asp调用简单例子:
< % Option Explicit % >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
< meta http - equiv = " Content-Type " content = " text/html; charset=gb2312 " />
< style >
body{
font - family: " 宋体 " ;
font - size: 9pt;
text - decoration: none;
line - height: 150 %;
margin: 0px;
background - color: #A8C2F9;
}
a:link { color: # 000000 ; text - decoration: none}
a:visited { color: # 000000 ; text - decoration: none}
a:hover { color: #3333ff; text - decoration: none}
</ style >
</ head >
< body >

< %
Dim strDbConn , Conn
strDBConn = " Provider=SQLOLEDB.1;Password=xxxxxxxxxx;Persist Security Info=True; " & _
" User ID=sa;Initial Catalog=DB_xxx;Data Source=RDG_NZ "
Set Conn = Server.CreateObject( " ADODB.Connection " )

Dim startime
startime = timer ()

Function PageView(pagecount,pagesize,page,recordercount)
' pagecount 总页数
' pagesize 每页显示数量
' page 当前是第几页
' recordercount 总记录数量
' get方式传值： pagecount:总页数 recordercount:总记录数量
pagecount = Clng (pagecount)
pagesize = Clng (pagesize)
page = Clng (page)
recordercount = Clng (recordercount)
Dim query, a, x, temp ,action
action = " http:// " & Request.ServerVariables( " HTTP_HOST " ) _
& Request.ServerVariables( " SCRIPT_NAME " )
query = Split (Request.ServerVariables( " QUERY_STRING " ), " & " )
For Each x In query
a = Split (x, " = " )
If StrComp (a( 0 ), " page " , vbTextCompare) <> 0 and _
StrComp (a( 0 ), " pagecount " , vbTextCompare) <> 0 _
and StrComp (a( 0 ), " recordercount " , vbTextCompare) <> 0 Then
temp = temp & a( 0 ) & " = " & a( 1 ) & " & "
End If
Next

PageView = " <form method=get οnsubmit=""document.location = ' " _
& action & " ? " & temp & " Page='+this.page.value;return false;""> "

If page <= 1 then
PageView = PageView & " [首页] [上一页] "
Else
PageView = PageView & " [<a href= " & action & " ? " & temp & " Page=1>首页</a>] "
PageView = PageView & " [<a href= " & action & " ? " & temp & " Page= " & (Page - 1 ) & _
" &pagecount= " & pagecount & " &recordercount= " & recordercount & " >上一页</a>] "
End If
If page >= pagecount then
PageView = PageView & " [下一页] [尾页] "
Else
PageView = PageView & " [<a href= " & action & " ? " & temp & " Page= " & (Page + 1 ) & _
" &pagecount= " & pagecount & " &recordercount= " & recordercount & " >下一页</a>] "
PageView = PageView & " [<a href= " & action & " ? " & temp & " Page= " & pagecount & _
" &pagecount= " & pagecount & " &recordercount= " & recordercount & " >尾页</a>] "
End If
PageView = PageView & " [页次:<font color=red> " & page & " </font>/ " & pageCount
PageView = PageView & " ] [共 " & recordercount & " 条 <font color=red> " & pagesize & _
" </font>条/页] "
PageView = PageView & " 转到 " & " <input name=page size=4 value= " & page & _
" > " & " 页<input type=submit value=go></form> "
End Function

Dim TableName ' 表名
Dim FieldList ' 显示列名，如果是全部字段则为*
Dim PrimaryKey ' 单一主键或唯一值键
Dim Where ' 查询条件 不含'where'字符，如id>10 and len(userid)>9
Dim order ' 排 序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
' 注意当Dim SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
Dim SortType ' 排序规则 1:正序asc 2:倒序desc 3:多列排序方法
Dim RecorderCount ' 记录总数 0:会返回总记录
Dim PageSize ' 每页输出的记录数
Dim PageIndex ' 当前页数
Dim TotalCount ' 记返回总记录
Dim TotalPageCount ' 记返回总页数

TableName = " T_TEST "
FieldList = " * "
PrimaryKey = " id "
Where = " id> " & request( " id " )
order = " id asc "
SortType = " 1 "
If Request( " recordercount " ) = "" Then
RecorderCount = " 0 "
Else
RecorderCount = Request( " recordercount " )
End If
PageSize = " 10 "
If Request( " page " ) = "" Then
PageIndex = " 1 "
Else
PageIndex = Request( " page " )
End If
TotalCount = RecorderCount
If Request( " pagecount " ) = "" Then
TotalPageCount = " 1 "
Else
TotalPageCount = Request( " pagecount " )
End If

Dim Cmd ,rs
Set Cmd = Server.CreateObject( " ADODB.Command " )
Set Rs = Server.CreateObject( " ADODB.Recordset " )
Conn.Open strDBConn
With Cmd
.CommandText = " P_viewPage "
.CommandType = 4
.CommandTimeout = 0
.ActiveConnection = Conn
.Parameters.Append .CreateParameter( " @TableName " , 200 , 1 , 200 ,TableName)
.Parameters.Append .CreateParameter( " @FieldList " , 200 , 1 , 2000 ,FieldList)
.Parameters.Append .CreateParameter( " @PrimaryKey " , 200 , 1 , 100 ,PrimaryKey)
.Parameters.Append .CreateParameter( " @Where " , 200 , 1 , 2000 ,Where)
.Parameters.Append .CreateParameter( " @Order " , 200 , 1 , 1000 ,Order)
.Parameters.Append .CreateParameter( " @SortType " , 3 , 1 ,,SortType)
.Parameters.Append .CreateParameter( " @RecorderCount " , 3 , 1 ,,RecorderCount)
.Parameters.Append .CreateParameter( " @PageSize " , 3 , 1 ,,PageSize)
.Parameters.Append .CreateParameter( " @PageIndex " , 3 , 1 ,,PageIndex)
.Parameters.Append .CreateParameter( " @TotalCount " , 3 , 3 ,,TotalCount)
.Parameters.Append .CreateParameter( " @TotalPageCount " , 3 , 3 ,,TotalPageCount)
Set rs = .Execute
If Not rs.Eof Then
while not rs.Eof
Rem 输出你的记录集内容
Response.Write rs( 0 ) & " || " & rs( 1 ) & " <br> "
rs.MoveNext
wend
End If
rs.Close
TotalCount = .Parameters( " @TotalCount " )
TotalPageCount = .Parameters( " @TotalPageCount " )
End With
Set Rs = Nothing
Set Cmd = Nothing
Conn.Close
Set Conn = Nothing
Response.write PageView(TotalPageCount,pagesize,PageIndex,TotalCount)

Dim endtime
endtime = timer ()
% >

< br > 页面执行时间： < % = FormatNumber ((endtime - startime) * 1000 , 3 )% > 毫秒
</ body >
</ html >

此文章来自 nzperfect