`
Sharpleo
  • 浏览: 572965 次
  • 性别: Icon_minigender_1
  • 来自: newsk
社区版块
存档分类
最新评论

sqlserver 几个分页存储过程

阅读更多

--使用系统存储过程实现的通用分页存储过程(转自邹建)
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

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

GO

 

 

 

 

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

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

 

/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
 

--邹建2003.09(引用请保留此信息)--*/

 

/*--调用示例
exec p_show '地区资料'

 

exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'

--*/

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') 

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_show]

GO

 

CREATE Proc p_show

@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)

@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as

declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int    --对象ID

--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
 

 

select @Obj_ID=object_id(@QueryStr)

,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end

,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end

 

--如果显示第一页,可以直接用top来完成
if @PageCurrent=1 

begin

select @Id1=cast(@PageSize as varchar(20))

exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)

return

end

 

--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1

begin

select @Id1=cast(@PageSize as varchar(20))

  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))

 

select @FdName=name from syscolumns where id=@Obj_ID and status=0x80

if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
begin

  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')

   goto lbusetemp  --如果表中无主键,则用临时表处理
 

  select @FdName=name from syscolumns where id=@Obj_ID and colid in(

   select colid from sysindexkeys where @Obj_ID=id and indid in(

    select indid from sysindexes where @Obj_ID=id and name in(

     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

   )))

  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin

   select @strfd='',@strjoin='',@strwhere=''

   select @strfd=@strfd+',['+name+']'

    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'

    ,@strwhere=@strwhere+' and b.['+name+'] is null'

    from syscolumns where id=@Obj_ID and colid in(

    select colid from sysindexkeys where @Obj_ID=id and indid in(

     select indid from sysindexes where @Obj_ID=id and name in(

      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID

    )))

   select @strfd=substring(@strfd,2,2000)

    ,@strjoin=substring(@strjoin,5,4000)

    ,@strwhere=substring(@strwhere,5,4000)

   goto lbusepk

  end

end

end

else

goto lbusetemp

 

/*--使用标识列或主键为单一字段的处理方法--*/

lbuseidentity: 

exec('select top '+@Id1+@FdShow+' from '+@QueryStr

  +' where '+@FdName+' not in(select top '

  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder

  +')'+@FdOrder

  )

return

 

/*--表中有复合主键的处理方法--*/

lbusepk:  

exec('select '+@FdShow+' from(select top '+@Id1+' a.* from

  (select top 100 percent * from '+@QueryStr+@FdOrder+') a

  left join (select top '+@Id2+' '+@strfd+' 

  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'

  where '+@strwhere+') a'

  )

return

 

/*--用临时表处理的方法--*/

lbusetemp:  

select @FdName='[ID_'+cast(newid() as varchar(40))+']'

,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))

,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))

 

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'

  into #tb from'+@QueryStr+@FdOrder+'

select '+@FdShow+' from #tb where '+@FdName+' between '

+@Id1+' and '+@Id2

)

GO


分享到:
评论

相关推荐

    标准SQLServer分页存储过程

    ### 标准SQL Server分页存储过程:深入解析与应用 在处理大量数据时,分页技术是提高数据展示效率和用户体验的关键。SQL Server提供的存储过程是实现分页功能的有效方式之一。本文将深入探讨微软C# .NET中的宠物...

    SQL Server 2005分页显示存储过程

    SQL Server 2005 提供了多种方法来实现分页查询,本文将详细介绍几种典型的分页显示存储过程,并比较它们之间的执行效率。 #### 核心知识点 ##### 1. 使用 `SELECT TOP` 和 `NOT IN` 实现分页 这种方法是通过先...

    Sql server 分页存储过程的性能对比

    在"分页存储过程效率对比.doc"文档中,可能详细分析了每种方法的执行计划、资源消耗、响应时间等指标,通过实验数据来展示不同方法在实际应用中的性能差异。对比这些数据可以帮助我们理解在特定数据库结构和工作负载...

    sqlserver 支持多表联合查询分页存储过程

    根据提供的信息,我们可以深入探讨如何在SQL Server中创建一个支持多表联合查询并实现分页功能的存储过程。本文将详细介绍存储过程的设计思路、参数解释、实现细节以及可能遇到的问题和解决方案。 ### 一、存储过程...

    sqlserver常用的存储过程

    本文介绍了 SQL Server 中几种常用存储过程的应用场景及其实现方式。通过这些存储过程,不仅可以提高应用程序的性能,还可以简化复杂的数据库操作,使得数据库的管理和维护更加便捷高效。在实际项目开发中,合理运用...

    sqlserver分页的存储过程

    本篇将详细讲解如何使用存储过程实现SQL Server的分页功能,以及描述中提到的具体应用。 首先,我们需要了解SQL Server中的分页查询基础。通常,我们使用`OFFSET`和`FETCH NEXT`语句来实现分页。这两个关键字允许...

    SQLServer 常用存储过程

    SQL Server 提供了一系列系统存储过程来执行各种管理任务,下面介绍几个常用的系统存储过程: **1. 给表中的字段添加描述信息** ```sql CREATE TABLE T2 (id int, name char(20)); GO EXEC sp_...

    万能分页存储过程

    本文将详细介绍一个名为“万能分页存储过程”的SQL Server存储过程,该存储过程可以实现在多种情况下的高效分页功能。 #### 二、存储过程定义 根据提供的部分代码内容,我们可以看出这是一个用于执行分页查询的存储...

    两个分页存储过程的效率比较

    在比较两个分页存储过程的效率时,我们需要考虑几个关键因素:一是动态SQL的构建和执行,这可能会对性能产生影响,尤其是当查询条件复杂时;二是处理排序逻辑的复杂性,特别是涉及数据类型的转换;三是事务处理的...

    sql 分页存储过程

    在给定的文件列表中,"多表分页存储过程-O.sql"可能包含了涉及多个表的分页查询存储过程。这种情况下,可能需要联接多个表并处理分页,这需要考虑到表之间的关联和性能优化。而"OmegaTestDB-O.sql"可能是测试数据库...

    ORACLE分页存储过程

    之前一直用SQL SERVER做项目,但是现在需要用到ORACLE,在网上找了几个都无法正常调用,因此自己写了一个,给大家一起分享,在写的过程中学习到了很多东西,同时也体会到了SQL SERVER和ORACLE中的异同点 在分页存储...

    SQLSERVER Pager store procedure分页存储过程

    存储过程的主要工作流程包括以下几个步骤: 1. **参数检查**:确保输入的参数有效,如表名、排序规则、页面大小和页码等不能为空,且页面大小和页码必须为正整数。 2. **构建SQL**:使用`ROW_NUMBER()`函数生成一...

    hiberate SQL Server 2000 存储过程.pdf

    标题与描述中的关键词“hibernate SQL Server 2000 存储过程”指出了本文档的主题,即在Hibernate框架下如何与SQL Server 2000的存储过程进行交互。下面将深入探讨这一主题,包括为什么在Hibernate中使用存储过程、...

    效率高的分页存储过程实现的分页

    本文将深入探讨如何通过使用存储过程来实现高效的分页存储过程,特别是针对ASP.NET应用程序。 标题 "效率高的分页存储过程实现的分页" 提到的是一种优化数据检索的技术。在ASP.NET开发中,GridView控件是常用的数据...

    分页SQLServer存储过程

    ### 分页SQL Server存储过程详解 #### 一、引言 在进行大量数据查询时,直接获取所有数据可能会导致性能问题。为了优化用户体验并减轻服务器压力,分页技术应运而生。本文将详细介绍一种利用SQL Server存储过程...

    海量数据分页sql server经典

    3. **改进的分页存储过程**:基于第二种方法的原理,可以进一步编写一个更加灵活和通用的存储过程。 ```sql CREATE PROCEDURE pagination2 ( @SQLn VARCHAR(4000), @Page INT, @RecsPerPage INT, @ID ...

    sql server分页技术(SQL Server 与Access数据库相关分页技术)

    以下是几种常用的SQL Server分页技术: 1. **TOP 和 NOT IN** 分页: 这种方法通过`TOP`关键字选取指定数量的记录,然后使用`NOT IN`子句排除已选取的记录。例如,查询第二页的记录,可以先选取前`@RecordStart`个...

    SQL Server交叉查询存储过程实现(带查询条件)

    SQL Server交叉查询存储过程实现(带查询条件) 一、交叉查询的概念 交叉查询是指将纵向的数据转换为横向的数据,以便更好地进行数据分析和展示。在SQL Server中,可以使用pivot函数或自定义函数来实现交叉查询。 ...

    经过优化测试的SQL分页存储过程.rar

    文档"经过优化测试的SQL分页存储过程.docx"很可能是对这个分页存储过程的详细解释,包括实现方法、性能测试结果和最佳实践。阅读这份文档可以帮助你理解如何设计和优化自己的SQL分页存储过程,从而提升数据库应用的...

    SQL 万能分页存储过程

    SQL 万能分页存储过程就是为了满足这一需求而设计的。它能够高效地处理分页查询,并同时提供查询总记录数的功能,对于开发人员来说,是一个非常实用的工具。 分页查询的基本原理是通过LIMIT和OFFSET(在MySQL中)...

Global site tag (gtag.js) - Google Analytics