网上收藏的几个邹老大的分页存储过程-优快云博客

-- TOP n 实现的通用分页存储过程(邹建)

CREATE PROC sp_PageView

@tbname sysname, -- 要分页显示的表名

@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int = 1 , -- 要显示的页码

@PageSize int = 10 , -- 每页的大小(记录数)

@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

用于指定排序顺序

@Where nvarchar ( 1000 ) = '' , -- 查询条件

@PageCount int OUTPUT -- 总页数

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

DECLARE @sql nvarchar ( 4000 )

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 - 1 ) * @PageSize

-- 第一页直接显示

IF @PageCurrent = 1

EXEC (N ' SELECT TOP ' + @TopN

+ N ' ' + @FieldShow

+ N ' FROM ' + @tbname

+ N ' ' + @Where

+ N ' ' + @FieldOrder )

ELSE

BEGIN

-- 处理别名

IF @FieldShow = N ' * '

SET @FieldShow = N ' a.* '

-- 生成主键(惟一键)处理条件

DECLARE @Where1 nvarchar ( 4000 ), @Where2 nvarchar ( 4000 ),

@s nvarchar ( 1000 ), @Field sysname

SELECT @Where1 = N '' , @Where2 = N '' , @s = @FieldKey

WHILE CHARINDEX (N ' , ' , @s ) > 0

SELECT @Field = LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 ),

@s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),

@Where1 = @Where1 + N ' AND a. ' + @Field + N ' =b. ' + @Field ,

@Where2 = @Where2 + N ' AND b. ' + @Field + N ' IS NULL ' ,

@Where = REPLACE ( @Where , @Field ,N ' a. ' + @Field ),

@FieldOrder = REPLACE ( @FieldOrder , @Field ,N ' a. ' + @Field ),

@FieldShow = REPLACE ( @FieldShow , @Field ,N ' a. ' + @Field )

SELECT @Where = REPLACE ( @Where , @s ,N ' a. ' + @s ),

@FieldOrder = REPLACE ( @FieldOrder , @s ,N ' a. ' + @s ),

@FieldShow = REPLACE ( @FieldShow , @s ,N ' a. ' + @s ),

@Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' =b. ' + @s , 1 , 5 ,N '' ),

@Where2 = CASE

WHEN @Where = '' THEN N ' WHERE ( '

ELSE @Where + N ' AND ( '

END + N ' b. ' + @s + N ' IS NULL ' + @Where2 + N ' ) '

-- 执行查询

EXEC (N ' SELECT TOP ' + @TopN

+ N ' ' + @FieldShow

+ N ' FROM ' + @tbname

+ N ' a LEFT JOIN(SELECT TOP ' + @TopN1

+ N ' ' + @FieldKey

+ N ' FROM ' + @tbname

+ N ' a ' + @Where

+ N ' ' + @FieldOrder

+ N ' )b ON ' + @Where1

+ N ' ' + @Where2

+ N ' ' + @FieldOrder )

END

-- 临时表缓存实现的通用分页存储过程(邹建)

CREATE PROC sp_PageView

@tbname sysname, -- 要分页显示的表名

@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int = 1 , -- 要显示的页码

@PageSize int = 10 , -- 每页的大小(记录数)

@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序

@Where nvarchar ( 1000 ) = '' , -- 查询条件

@PageCount int OUTPUT -- 总页数

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 ' ) '

IF @PageCount IS NULL

BEGIN

DECLARE @sql nvarchar ( 4000 )

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

-- 生成主键(惟一键)处理条件

DECLARE @Where1 nvarchar ( 4000 ), @s nvarchar ( 1000 )

SELECT @Where1 = N '' , @s = @FieldKey

WHILE CHARINDEX (N ' , ' , @s ) > 0

SELECT @s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),

@Where1 = @Where1

+ N ' AND a. ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )

+ N ' = ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )

SELECT @Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' = ' + @s , 1 , 5 ,N '' ),

@TopN = @TopN1 - @PageSize

-- 执行查询

EXEC (N ' SET ROWCOUNT ' + @TopN1

+ N ' SELECT ' + @FieldKey

+ N ' INTO # FROM ' + @tbname

+ N ' ' + @Where

+ N ' ' + @FieldOrder

+ N ' SET ROWCOUNT ' + @TopN

+ N ' DELETE FROM # '

+ N ' SELECT ' + @FieldShow

+ N ' FROM ' + @tbname

+ N ' a WHERE EXISTS(SELECT * FROM # WHERE ' + @Where1

+ N ' ) ' + @FieldOrder )

END

-- 字符串缓存实现的通用分页存储过程(邹建)

CREATE PROC sp_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 -- 总页数

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 ' ) '

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

-- 使用系统存储过程实现的通用分页存储过程(邹建)

CREATE PROC sp_PageView

@sql ntext, -- 要执行的sql语句

@PageCurrent int = 1 , -- 要显示的页码

@PageSize int = 10 , -- 每页的大小

@PageCount int OUTPUT -- 总页数

SET NOCOUNT ON

DECLARE @p1 int

-- 初始化分页游标

EXEC sp_cursoropen

@cursor = @p1 OUTPUT,

@stmt = @sql,

@scrollopt = 1 ,

@ccopt = 1 ,

@rowcount = @PageCount OUTPUT

-- 计算总页数

IF ISNULL(@PageSize, 0 ) < 1

SET @PageSize = 10

SET @PageCount = (@PageCount + @PageSize - 1 ) / @PageSize

IF ISNULL(@PageCurrent, 0 ) < 1 OR ISNULL(@PageCurrent, 0 ) > @PageCount

SET @PageCurrent = 1

ELSE

SET @PageCurrent = (@PageCurrent - 1 ) * @PageSize + 1

-- 显示指定页的数据

EXEC sp_cursorfetch @p1, 16 ,@PageCurrent,@PageSize

-- 关闭分页游标

EXEC sp_cursorclose @p1

-- 根据分类表实现的分页存储过程(邹建)

-- 要分页的原始数据

CREATE TABLE tb(

ID int PRIMARY KEY, -- 记录编号

grade varchar( 10 ), -- 类别名称

uptime datetime) -- 更新时间

INSERT tb SELECT 1 , ' a ' , ' 2004-12-11 '

UNION ALL SELECT 2 , ' b ' , ' 2004-12-11 '

UNION ALL SELECT 3 , ' c ' , ' 2004-12-11 '

UNION ALL SELECT 4 , ' a ' , ' 2004-12-12 '

UNION ALL SELECT 5 , ' c ' , ' 2004-12-13 '

UNION ALL SELECT 6 , ' c ' , ' 2004-12-13 '

UNION ALL SELECT 7 , ' a ' , ' 2004-12-14 '

UNION ALL SELECT 8 , ' a ' , ' 2004-12-15 '

UNION ALL SELECT 9 , ' b ' , ' 2004-12-16 '

UNION ALL SELECT 10 , ' b ' , ' 2004-12-17 '

UNION ALL SELECT 11 , ' a ' , ' 2004-12-17 '

-- 分页定义表

CREATE TABLE tb_Page(

grade varchar( 10 ) PRIMARY KEY, -- 类别名称,与tb表的grade关联

Records int , -- 每页显示的记录数

Orders int ) -- 在页中的显示顺序

INSERT tb_Page SELECT ' c ' , 2 , 1

UNION ALL SELECT ' b ' , 1 , 2

UNION ALL SELECT ' a ' , 2 , 3

-- 实现分页处理的存储过程

CREATE PROC p_PageView

@PageCurrent int = 1 -- 要显示的当前页码

SET NOCOUNT ON

-- 得到每页的记录数

DECLARE @PageSize int

SELECT @PageSize = SUM(Records) FROM tb_Page

IF ISNULL(@PageSize, 0 ) < 0 RETURN

-- 分页显示处理

SET @PageCurrent = @PageCurrent * @PageSize

SET ROWCOUNT @PageCurrent

SELECT SID = IDENTITY( int , 1 , 1 ),ID

INTO # FROM(

SELECT TOP 100 PERCENT a.ID

FROM tb a

LEFT JOIN tb_Page b ON a.grade = b.grade

ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END, -- 分类没有定义的显示在最后

((SELECT COUNT( * ) FROM tb

WHERE grade = a.grade

AND (uptime > a.uptime OR uptime = a.uptime AND id >= a.id)) - 1 )

/ b.Records,

b.Orders,a.ID DESC)a

IF @PageCurrent > @PageSize

BEGIN

SET @PageCurrent = @PageCurrent - @PageSize

SET ROWCOUNT @PageCurrent

DELETE FROM #

END

SELECT a. * FROM tb a,# b

WHERE a.ID = b.ID

ORDER BY b.SID

-- 调用

EXEC p_PageView 2

/*--结果

ID grade uptime

----------- ---------- ------------------------------------------------------

3 c 2004-12-11 00:00:00.000

9 b 2004-12-16 00:00:00.000

7 a 2004-12-14 00:00:00.000

4 a 2004-12-12 00:00:00.000

2 b 2004-12-11 00:00:00.000

--*/

还有一个,论坛里找的,不是老大的:

CREATE PROCEDURE SP_Page

@TB VARCHAR ( 50 ),

@COL VARCHAR ( 50 ),

-- @COLTYPE INT,

@ORDERBY BIT ,

@COLLIST VARCHAR ( 800 ),

@PAGESIZE INT ,

@PAGE INT ,

@CONDITION VARCHAR ( 800 ),

@RecPages INT ,

@RecCount INT OUTPUT,

@PAGES INT OUTPUT,

@OUTSQL NVARCHAR ( 4000 ) OUTPUT

DECLARE @SQL NVARCHAR ( 4000 )

DECLARE @WHERE1 VARCHAR ( 800 )

DECLARE @WHERE2 VARCHAR ( 800 )

IF @CONDITION IS NULL OR RTRIM ( @CONDITION ) = ''

BEGIN

SET @WHERE1 = ' WHERE '

SET @WHERE2 = ' '

END

ELSE

BEGIN

SET @WHERE1 = ' WHERE ( ' + @CONDITION + ' ) AND '

SET @WHERE2 = ' WHERE ( ' + @CONDITION + ' ) '

END

SET @SQL = ' SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/ ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ) FROM ' + @TB + @WHERE2

IF @RecPages = 0

EXEC SP_EXECUTESQL @SQL ,N ' @PAGES INT OUTPUT,@RecCount INT OUTPUT ' , @PAGES OUTPUT, @RecCount OUTPUT

ELSE

SELECT @PAGES = @RecPages

IF @ORDERBY = 0

SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' >(SELECT MAX( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +

@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' ) t) ORDER BY ' + @COL

ELSE

SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' <(SELECT MIN( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +

@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' DESC) t) ORDER BY ' + @COL + ' DESC '

IF @PAGE = 1

SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC ' END

SET @OUTSQL = @SQL

EXEC ( @SQL )