分页存储过程
**/
/*
说明:1.支持多表查询 2.支持任意排序 3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程(效果演示)
地址:http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
DROP PROCEDURE usp_PagingLarge
*/
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR ( 200 ), -- 表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR ( 100 ), -- 主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR ( 200 ), -- 要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT , -- 每页记录数
@CurrentPage INT , -- 当前页,0表示第1页
@Filter VARCHAR ( 200 ) = '' , -- 条件,可以为空,不用填 where
@Group VARCHAR ( 200 ) = '' , -- 分组依据,可以为空,不用填 group by
@Order VARCHAR ( 200 ) = '' -- 排序,可以为空,为空默认按主键升序排列,不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR ( 200 )
DECLARE @Operator CHAR ( 2 )
DECLARE @SortTable VARCHAR ( 200 )
DECLARE @SortName VARCHAR ( 200 )
IF @Fields = ''
SET @Fields = ' * '
IF @Filter = ''
SET @Filter = ' WHERE 1=1 '
ELSE
SET @Filter = ' WHERE ' + @Filter
IF @Group <> ''
SET @Group = ' GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT , @pos2 INT
SET @Order = REPLACE ( REPLACE ( @Order , ' asc ' , ' ASC ' ), ' desc ' , ' DESC ' )
IF CHARINDEX ( ' DESC ' , @Order ) > 0
IF CHARINDEX ( ' ASC ' , @Order ) > 0
BEGIN
IF CHARINDEX ( ' DESC ' , @Order ) < CHARINDEX ( ' ASC ' , @Order )
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
END
ELSE
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
SET @SortColumn = REPLACE ( REPLACE ( REPLACE ( @Order , ' ASC ' , '' ), ' DESC ' , '' ), ' ' , '' )
SET @pos1 = CHARINDEX ( ' , ' , @SortColumn )
IF @pos1 > 0
SET @SortColumn = SUBSTRING ( @SortColumn , 1 , @pos1 - 1 )
SET @pos2 = CHARINDEX ( ' . ' , @SortColumn )
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING ( @SortColumn , 1 , @pos2 - 1 )
IF @pos1 > 0
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , @pos1 - @pos2 - 1 )
ELSE
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , LEN ( @SortColumn ) - @pos2 )
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = ' >= '
END
DECLARE @type varchar ( 50 )
DECLARE @prec int
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC ( '
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin= ' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + ' @SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
' )
END
GO
-- 调用例子:
-- 1.单表/单排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' d_id,d_title,d_content,d_time ' , 20 , 1 , '' , '' , ' d_id desc '
-- 2.单表/多排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' * ' , 20 , 0 , '' , '' , ' d_time asc,d_id desc '
-- 3.多表/单排序
EXEC usp_PagingLarge ' bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_id asc '
-- 4.多表/多排序
EXEC usp_PagingLarge ' bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_time asc,bigtable.d_id desc '
/Files/jiny-z/Paging_Custom.rar
说明:1.支持多表查询 2.支持任意排序 3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程(效果演示)
地址:http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
DROP PROCEDURE usp_PagingLarge
*/
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR ( 200 ), -- 表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR ( 100 ), -- 主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR ( 200 ), -- 要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT , -- 每页记录数
@CurrentPage INT , -- 当前页,0表示第1页
@Filter VARCHAR ( 200 ) = '' , -- 条件,可以为空,不用填 where
@Group VARCHAR ( 200 ) = '' , -- 分组依据,可以为空,不用填 group by
@Order VARCHAR ( 200 ) = '' -- 排序,可以为空,为空默认按主键升序排列,不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR ( 200 )
DECLARE @Operator CHAR ( 2 )
DECLARE @SortTable VARCHAR ( 200 )
DECLARE @SortName VARCHAR ( 200 )
IF @Fields = ''
SET @Fields = ' * '
IF @Filter = ''
SET @Filter = ' WHERE 1=1 '
ELSE
SET @Filter = ' WHERE ' + @Filter
IF @Group <> ''
SET @Group = ' GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT , @pos2 INT
SET @Order = REPLACE ( REPLACE ( @Order , ' asc ' , ' ASC ' ), ' desc ' , ' DESC ' )
IF CHARINDEX ( ' DESC ' , @Order ) > 0
IF CHARINDEX ( ' ASC ' , @Order ) > 0
BEGIN
IF CHARINDEX ( ' DESC ' , @Order ) < CHARINDEX ( ' ASC ' , @Order )
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
END
ELSE
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
SET @SortColumn = REPLACE ( REPLACE ( REPLACE ( @Order , ' ASC ' , '' ), ' DESC ' , '' ), ' ' , '' )
SET @pos1 = CHARINDEX ( ' , ' , @SortColumn )
IF @pos1 > 0
SET @SortColumn = SUBSTRING ( @SortColumn , 1 , @pos1 - 1 )
SET @pos2 = CHARINDEX ( ' . ' , @SortColumn )
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING ( @SortColumn , 1 , @pos2 - 1 )
IF @pos1 > 0
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , @pos1 - @pos2 - 1 )
ELSE
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , LEN ( @SortColumn ) - @pos2 )
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = ' >= '
END
DECLARE @type varchar ( 50 )
DECLARE @prec int
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC ( '
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin= ' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + ' @SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
' )
END
GO
-- 调用例子:
-- 1.单表/单排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' d_id,d_title,d_content,d_time ' , 20 , 1 , '' , '' , ' d_id desc '
-- 2.单表/多排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' * ' , 20 , 0 , '' , '' , ' d_time asc,d_id desc '
-- 3.多表/单排序
EXEC usp_PagingLarge ' bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_id asc '
-- 4.多表/多排序
EXEC usp_PagingLarge ' bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_time asc,bigtable.d_id desc '
与自定义分页结合例子:
/Files/jiny-z/Paging_Custom.rar
alter
PROCEDURE
SP_Pagination
/*
***************************************************************
** 分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields:字段
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
8.docount: 1返回总行数,0返回列表
***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL ,
@docount bit = 0
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 200 )
DECLARE @strSimpleFilter varchar ( 200 )
DECLARE @strGroup varchar ( 200 )
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
declare @cTemp NVarChar ( 1000 )
declare @PageCount int , @lineCount decimal
CREATE TABLE # temp (linecount INT )
set @cTemp = ' insert into #temp (linecount) select count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup
exec ( @cTemp )
select @lineCount = linecount from # temp
drop table # temp
if ( @docount = 1 )
begin
select @lineCount ' 总行数 '
end
else
begin
-- 得到总页数
set @PageCount = CEILING ( @lineCount / @strPageSize )
if @CurrentPage > @PageCount
begin
set @cTemp = ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1>2 '
end
else
begin
/*执行查询语句*/
set @cTemp = ' DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ''
end
-- print @cTemp
EXEC ( @cTemp )
end
-- ---------------------------------------------------
/*
***************************************************************
** 分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields:字段
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
8.docount: 1返回总行数,0返回列表
***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL ,
@docount bit = 0
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 200 )
DECLARE @strSimpleFilter varchar ( 200 )
DECLARE @strGroup varchar ( 200 )
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
declare @cTemp NVarChar ( 1000 )
declare @PageCount int , @lineCount decimal
CREATE TABLE # temp (linecount INT )
set @cTemp = ' insert into #temp (linecount) select count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup
exec ( @cTemp )
select @lineCount = linecount from # temp
drop table # temp
if ( @docount = 1 )
begin
select @lineCount ' 总行数 '
end
else
begin
-- 得到总页数
set @PageCount = CEILING ( @lineCount / @strPageSize )
if @CurrentPage > @PageCount
begin
set @cTemp = ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1>2 '
end
else
begin
/*执行查询语句*/
set @cTemp = ' DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ''
end
-- print @cTemp
EXEC ( @cTemp )
end
-- ---------------------------------------------------
分页查询的方法已经很多很多,在这里我也加入成为其中一员。
SQL Server中有一个Set Rowcount的的设置,它的意思是使命令的处理在响应指定的行数之后停止处理命令,利用这个特点,我们可以借用它来在一个千万行级数据表中实现高性能分页查询。先来说说实现方式:
1 、我们来假定Table中有一个已经建立了索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。
2 、页的大小我们放在 @PageSize中
3 、当前页号我们放在 @CurrentPage中
4 、如何让记录指针快速滚动到我们要取的数据开头的那一行呢,这是关键所在!有了Set RowCount ,我们就很容易实现了。
5 、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行,然后我们把那一行的记录的ID字段的值记录下来,那么,利用Top和条件,我们就很容易的得到指定页的数据了。当然,有了Set RowCount ,我们难道还用Top么?
看看Set Rowcount怎么来帮我们的忙吧:
Declare @ID int
Declare @MoveRecords int
-- @CurrentPage和@PageSize是传入参数
Set @MoveRecords = @CurrentPage * @PageSize + 1
-- 下面两行实现快速滚动到我们要取的数据的行,并把ID记录下来
Set Rowcount @MoveRecords
Select @ID = ID from Table1 Order by ID
Set Rowcount @PageSize
-- 最恨为了减少麻烦使用*了,但是在这里为了说明方便,暂时用一下
Select * From Table1 Where ID >= @ID Order By ID
Set Rowcount 0
大家可以试试看,在一个1千W记录的表里面,一下子方翻页到第100页(每页100条),看看有多快!
SQL Server中有一个Set Rowcount的的设置,它的意思是使命令的处理在响应指定的行数之后停止处理命令,利用这个特点,我们可以借用它来在一个千万行级数据表中实现高性能分页查询。先来说说实现方式:
1 、我们来假定Table中有一个已经建立了索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。
2 、页的大小我们放在 @PageSize中
3 、当前页号我们放在 @CurrentPage中
4 、如何让记录指针快速滚动到我们要取的数据开头的那一行呢,这是关键所在!有了Set RowCount ,我们就很容易实现了。
5 、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行,然后我们把那一行的记录的ID字段的值记录下来,那么,利用Top和条件,我们就很容易的得到指定页的数据了。当然,有了Set RowCount ,我们难道还用Top么?
看看Set Rowcount怎么来帮我们的忙吧:
Declare @ID int
Declare @MoveRecords int
-- @CurrentPage和@PageSize是传入参数
Set @MoveRecords = @CurrentPage * @PageSize + 1
-- 下面两行实现快速滚动到我们要取的数据的行,并把ID记录下来
Set Rowcount @MoveRecords
Select @ID = ID from Table1 Order by ID
Set Rowcount @PageSize
-- 最恨为了减少麻烦使用*了,但是在这里为了说明方便,暂时用一下
Select * From Table1 Where ID >= @ID Order By ID
Set Rowcount 0
大家可以试试看,在一个1千W记录的表里面,一下子方翻页到第100页(每页100条),看看有多快!
分享]千万数量级分页存储过程(带效果演示)
效果演示:http: // www.cn5135.com / _App / Opportunities / QueryResult.aspx
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 1000 )
DECLARE @strSimpleFilter varchar ( 1000 )
DECLARE @strGroup varchar ( 1000 )
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC (
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
效果演示:http: // www.cn5135.com / _App / Opportunities / QueryResult.aspx
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 1000 )
DECLARE @strSimpleFilter varchar ( 1000 )
DECLARE @strGroup varchar ( 1000 )
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC (
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3) 对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
1
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[Paging_RowCount]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
2 drop procedure [ dbo ] . [ Paging_RowCount ]
3 GO
4
5 SET QUOTED_IDENTIFIER ON
6 GO
7 SET ANSI_NULLS ON
8 GO
9 -- -------------------------------------------------------------
10 -- 分页存储过程(使用RowCount) --edit by SiBen
11 -- summary:
12 -- 获取表或表集合的分页数据
13 -- 当多表连接时,sort列必须指定表名
14 -- -------------------------------------------------------------
15
16 CREATE PROCEDURE Paging_RowCount
17 (
18 @Tables varchar ( 1000 ),
19 @PK varchar ( 100 ),
20 @Sort varchar ( 200 ) = NULL ,
21 @PageNumber int = 1 ,
22 @PageSize int = 10 ,
23 @Fields varchar ( 1000 ) = ' * ' ,
24 @Filter varchar ( 1000 ) = NULL ,
25 @Group varchar ( 1000 ) = NULL ,
26 @RecordCount int = 0 output
27 )
28 AS
29
30 /*Default Sorting*/
31 IF @Sort IS NULL OR @Sort = ''
32 SET @Sort = @PK
33
34 /*Find the @PK type*/
35 DECLARE @SortTable varchar ( 100 )
36 DECLARE @SortName varchar ( 100 )
37 DECLARE @strSortColumn varchar ( 200 )
38 DECLARE @operator char ( 2 )
39 DECLARE @type varchar ( 100 )
40 DECLARE @prec int
41
42 /*Set sorting variables.*/
43 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
44 BEGIN
45 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
46 SET @operator = ' < '
47 END
48 ELSE
49 BEGIN
50 IF CHARINDEX ( ' ASC ' , @Sort ) > 0
51 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
52 ELSE
53 SET @strSortColumn = @Sort
54
55 SET @operator = ' > '
56 END
57
58
59 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
60 BEGIN
61 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
62 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
63 END
64 ELSE
65 BEGIN
66 SET @SortTable = @Tables
67 SET @SortName = @strSortColumn
68 END
69
70 SELECT @type = t.name, @prec = c.prec
71 FROM sysobjects o
72 JOIN syscolumns c on o.id = c.id
73 JOIN systypes t on c.xusertype = t.xusertype
74 WHERE o.name = @SortTable AND c.name = @SortName
75
76 IF CHARINDEX ( ' char ' , @type ) > 0
77 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
78
79 DECLARE @strPageSize varchar ( 50 )
80 DECLARE @strStartRow varchar ( 50 )
81 DECLARE @strFilter varchar ( 1000 )
82 DECLARE @strSimpleFilter varchar ( 1000 )
83 DECLARE @strGroup varchar ( 1000 )
84
85 /*Default Page Number*/
86 IF @PageNumber < 1
87 SET @PageNumber = 1
88
89 /*Set paging variables.*/
90 SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
91 SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
92
93 /*Set filter & group variables.*/
94 IF @Filter IS NOT NULL AND @Filter != ''
95 BEGIN
96 SET @strFilter = ' WHERE ' + @Filter + ' '
97 SET @strSimpleFilter = ' AND ' + @Filter + ' '
98 END
99 ELSE
100 BEGIN
101 SET @strSimpleFilter = ''
102 SET @strFilter = ''
103 END
104 IF @Group IS NOT NULL AND @Group != ''
105 SET @strGroup = ' GROUP BY ' + @Group + ' '
106 ELSE
107 SET @strGroup = ''
108
109 /*Get rows count.*/
110 DECLARE @str_Count_SQL nvarchar ( 500 )
111 SET @str_Count_SQL = ' SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
112 EXEC sp_executesql @str_Count_SQL ,N ' @TotalCount int=0 output ' , @RecordCount output
113
114 /*Execute dynamic query*/
115 IF @Sort = @PK
116 BEGIN
117 EXEC (
118 '
119 DECLARE @SortColumn ' + @type + '
120 SET ROWCOUNT ' + @strStartRow + '
121 SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122 SET ROWCOUNT ' + @strPageSize + '
123 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' = @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124 '
125 )
126 END
127 ELSE
128 BEGIN
129 EXEC (
130 '
131 DECLARE @SortColumn ' + @type + '
132 DECLARE @SortNullValue ' + @type + '
133 DECLARE @PKStartValue int
134 SET @SortNullValue=CAST( '''' as ' + @type + ' )
135 SET ROWCOUNT ' + @strStartRow + '
136 SELECT @SortColumn= isNull( ' + @strSortColumn + ' ,@SortNullValue), @PKStartValue = ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
137 SET ROWCOUNT ' + @strPageSize + '
138 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull( ' + @strSortColumn + ' ,@SortNullValue) ' + @operator + ' @SortColumn or (isNull( ' + @strSortColumn + ' ,@SortNullValue)=@SortColumn and ' + @PK + ' <=@PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
139 '
140 )
141 END
142 GO
143 SET QUOTED_IDENTIFIER OFF
144 GO
145 SET ANSI_NULLS ON
146 GO
147
148
http://www.codeproject.com/aspnet/PagingLarge.asp
2 drop procedure [ dbo ] . [ Paging_RowCount ]
3 GO
4
5 SET QUOTED_IDENTIFIER ON
6 GO
7 SET ANSI_NULLS ON
8 GO
9 -- -------------------------------------------------------------
10 -- 分页存储过程(使用RowCount) --edit by SiBen
11 -- summary:
12 -- 获取表或表集合的分页数据
13 -- 当多表连接时,sort列必须指定表名
14 -- -------------------------------------------------------------
15
16 CREATE PROCEDURE Paging_RowCount
17 (
18 @Tables varchar ( 1000 ),
19 @PK varchar ( 100 ),
20 @Sort varchar ( 200 ) = NULL ,
21 @PageNumber int = 1 ,
22 @PageSize int = 10 ,
23 @Fields varchar ( 1000 ) = ' * ' ,
24 @Filter varchar ( 1000 ) = NULL ,
25 @Group varchar ( 1000 ) = NULL ,
26 @RecordCount int = 0 output
27 )
28 AS
29
30 /*Default Sorting*/
31 IF @Sort IS NULL OR @Sort = ''
32 SET @Sort = @PK
33
34 /*Find the @PK type*/
35 DECLARE @SortTable varchar ( 100 )
36 DECLARE @SortName varchar ( 100 )
37 DECLARE @strSortColumn varchar ( 200 )
38 DECLARE @operator char ( 2 )
39 DECLARE @type varchar ( 100 )
40 DECLARE @prec int
41
42 /*Set sorting variables.*/
43 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
44 BEGIN
45 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
46 SET @operator = ' < '
47 END
48 ELSE
49 BEGIN
50 IF CHARINDEX ( ' ASC ' , @Sort ) > 0
51 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
52 ELSE
53 SET @strSortColumn = @Sort
54
55 SET @operator = ' > '
56 END
57
58
59 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
60 BEGIN
61 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
62 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
63 END
64 ELSE
65 BEGIN
66 SET @SortTable = @Tables
67 SET @SortName = @strSortColumn
68 END
69
70 SELECT @type = t.name, @prec = c.prec
71 FROM sysobjects o
72 JOIN syscolumns c on o.id = c.id
73 JOIN systypes t on c.xusertype = t.xusertype
74 WHERE o.name = @SortTable AND c.name = @SortName
75
76 IF CHARINDEX ( ' char ' , @type ) > 0
77 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
78
79 DECLARE @strPageSize varchar ( 50 )
80 DECLARE @strStartRow varchar ( 50 )
81 DECLARE @strFilter varchar ( 1000 )
82 DECLARE @strSimpleFilter varchar ( 1000 )
83 DECLARE @strGroup varchar ( 1000 )
84
85 /*Default Page Number*/
86 IF @PageNumber < 1
87 SET @PageNumber = 1
88
89 /*Set paging variables.*/
90 SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
91 SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
92
93 /*Set filter & group variables.*/
94 IF @Filter IS NOT NULL AND @Filter != ''
95 BEGIN
96 SET @strFilter = ' WHERE ' + @Filter + ' '
97 SET @strSimpleFilter = ' AND ' + @Filter + ' '
98 END
99 ELSE
100 BEGIN
101 SET @strSimpleFilter = ''
102 SET @strFilter = ''
103 END
104 IF @Group IS NOT NULL AND @Group != ''
105 SET @strGroup = ' GROUP BY ' + @Group + ' '
106 ELSE
107 SET @strGroup = ''
108
109 /*Get rows count.*/
110 DECLARE @str_Count_SQL nvarchar ( 500 )
111 SET @str_Count_SQL = ' SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
112 EXEC sp_executesql @str_Count_SQL ,N ' @TotalCount int=0 output ' , @RecordCount output
113
114 /*Execute dynamic query*/
115 IF @Sort = @PK
116 BEGIN
117 EXEC (
118 '
119 DECLARE @SortColumn ' + @type + '
120 SET ROWCOUNT ' + @strStartRow + '
121 SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122 SET ROWCOUNT ' + @strPageSize + '
123 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' = @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124 '
125 )
126 END
127 ELSE
128 BEGIN
129 EXEC (
130 '
131 DECLARE @SortColumn ' + @type + '
132 DECLARE @SortNullValue ' + @type + '
133 DECLARE @PKStartValue int
134 SET @SortNullValue=CAST( '''' as ' + @type + ' )
135 SET ROWCOUNT ' + @strStartRow + '
136 SELECT @SortColumn= isNull( ' + @strSortColumn + ' ,@SortNullValue), @PKStartValue = ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
137 SET ROWCOUNT ' + @strPageSize + '
138 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull( ' + @strSortColumn + ' ,@SortNullValue) ' + @operator + ' @SortColumn or (isNull( ' + @strSortColumn + ' ,@SortNullValue)=@SortColumn and ' + @PK + ' <=@PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
139 '
140 )
141 END
142 GO
143 SET QUOTED_IDENTIFIER OFF
144 GO
145 SET ANSI_NULLS ON
146 GO
147
148
邹建的存储过程
ALTER PROC PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey sysname, -- 用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
-- 用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
DECLARE @sql nvarchar ( 4000 )
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
SELECT @PageCurrent = @TopN1 ,
@sql = N ' SELECT @n=@n-1,@s=CASE WHEN @n< ' + @TopN
+ N ' THEN @s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
+ N ' as varchar(8000))),N '''''''' ) ELSE N '''' END FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql ,
N ' @n int,@s nvarchar(4000) OUTPUT ' ,
@PageCurrent , @sql OUTPUT
SET ROWCOUNT 0
IF @sql = N ''
EXEC (N ' SELECT TOP 0 '
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname )
ELSE
BEGIN
SET @sql = STUFF ( @sql , 1 , 1 ,N '' )
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' WHERE ' + @FieldKey
+ N ' IN( ' + @sql
+ N ' ) ' + @FieldOrder )
END
END
http://www.cnblogs.com/ghd258/archive/2006/01/11/314988.html
ALTER PROC PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey sysname, -- 用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
-- 用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
DECLARE @sql nvarchar ( 4000 )
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
SELECT @PageCurrent = @TopN1 ,
@sql = N ' SELECT @n=@n-1,@s=CASE WHEN @n< ' + @TopN
+ N ' THEN @s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
+ N ' as varchar(8000))),N '''''''' ) ELSE N '''' END FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql ,
N ' @n int,@s nvarchar(4000) OUTPUT ' ,
@PageCurrent , @sql OUTPUT
SET ROWCOUNT 0
IF @sql = N ''
EXEC (N ' SELECT TOP 0 '
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname )
ELSE
BEGIN
SET @sql = STUFF ( @sql , 1 , 1 ,N '' )
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' WHERE ' + @FieldKey
+ N ' IN( ' + @sql
+ N ' ) ' + @FieldOrder )
END
END
0
0
(请您对文章做出评价)