网上收藏的几个邹老大的分页存储过程

原创 于 2007-10-25 13:44:00 发布 · 1.3k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#存储 #output #sql #object #null #go

--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             --总页数
AS
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           --总页数
AS
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*@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            --总页数
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

 

--使用系统存储过程实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView   
@sql         ntext,     --要执行的sql语句
@PageCurrent int=1,     --要显示的页码
@PageSize    int=10,    --每页的大小
@PageCount   int OUTPUT --总页数
AS
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
GO

--实现分页处理的存储过程
CREATE PROC p_PageView
@PageCurrent int=1  --要显示的当前页码
AS
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
GO

--调用
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

AS

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

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值