通用分页存储过程源码共享大家共同完善

2016-02-19 11:48 3 1 收藏

下面图老师小编跟大家分享通用分页存储过程源码共享大家共同完善,一起来学习下过程究竟如何进行吧!喜欢就赶紧收藏起来哦~

【 tulaoshi.com - 编程语言 】

好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本上不能够满足一些复杂的SQL语句的分页(也可能是我不够见多识广啊,呵呵),比如下面这句: 
select '' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10), B.BeginDate, 120) as BeginDate, 
    Convert(varchar(10), B.EndDate, 120) as EndDate, C.SalesCode, C.SalesName, D.CatalogCode, D.CatalogName, 
    E.OrgID, E.OrgName, F.OrgID as BranchOrgID, F.OrgCode as BranchOrgCode, F.OrgName as BranchOrgName, 
    A.Amount, '' as DetailButton
from ChlSalesTarget as A
    left outer join ChlSalesTargetPeriod as B on A.TargetPeriod=B.TargetPeriod
    left outer join ChlSales as C on A.Sales=C.SalesCode
    left outer join ChlItemCatalog as D on A.ItemCatalog=D.CatalogCode
    left outer join ChlOrg as E on A.OrgID=E.OrgID
    left outer join ChlOrg as F on C.BranchOrgID=F.OrgID
where A.TargetPeriod ='200607' and A.TargetPeriod ='200608' and F.OrgCode like '%123%' and E.OrgCode like '%123%'
order by A.TargetPeriod desc,C.SalesName,D.CatalogName上面这句SQL里面有一些特殊情况,比如使用了Convert函数,而且没有主键,有多表连接,有表别名,字段别名等等,这些情况处理起来可能比较棘手,当然,其中的“'' as CheckBox”是我系统当中的特例情况,用来做一些处理的。
    我这里提供一个自己开发的通用分页存储过程,有什么好的建议和意见,大家请不吝指教。代码如下:
通用分页存储过程----Sp_Paging
/**//*
============================================================
功能:    通用分页存储过程
参数:
    @PK    varchar(50),            主键,用来排序的单一字段,空的话,表示没有主键,存储过程将自动创建标识列主键
    @Fields    varchar(500),        要显示的字段列表(格式如:ID,Code,Name)
    @Tables varchar(1000),        要使用的表集合(Org)
    @Where varchar(500),        查询条件(Code like '100')
    @OrderBy varchar(100),        排序条件(支持多个排序字段,如:ID,Code desc,Name desc)
    @PageIndex int,                当前要显示的页的页索引,索引从1开始,无记录时为0。
    @PageSize int,                页大小
创建者:Hollis Yao
创建日期:2006-08-06
备注:
============================================================
*/
Create PROCEDURE [dbo].[Sp_Paging]
    @PK    varchar(50)='',
    @Fields    varchar(500),
    @Tables varchar(1000),
    @Where varchar(500)='',
    @OrderBy varchar(100),
    @PageIndex int,
    @PageSize int
AS
--替换单引号,避免构造SQL出错
set @Fields = replace(@Fields, '''', '''''')

--要执行的SQL,切分为几个字符串,避免出现长度超过4k时的问题
declare @SQL1 varchar(4000)
declare @SQL2 varchar(4000)

set @SQL1 = ''
set @SQL2 = ''
if @Where is not null and len(ltrim(rtrim(@Where)))  0
    set @Where = ' where ' + @Where
else
    set @Where = ' where 1=1'

set @SQL1 = @SQL1 + ' declare @TotalCount int'    --声明一个变量,总记录数
set @SQL1 = @SQL1 + ' declare @PageCount int'    --声明一个变量,总页数
set @SQL1 = @SQL1 + ' declare @PageIndex int'    --声明一个变量,页索引
set @SQL1 = @SQL1 + ' declare @StartRow int'    --声明一个变量,当前页第一条记录的索引

set @SQL1 = @SQL1 + ' select @TotalCount=count(*) from ' + @Tables + @Where    --获取总记录数
set @SQL1 = @SQL1 + ' if @PageCount = 0 begin'    --如果记录数为0,直接输出空的结果集
set @SQL1 = @SQL1 + ' select ' + @Fields + ' from ' + @Tables + ' where 11'
set @SQL1 = @SQL1 + ' select 0 as PageIndex,0 as PageCount,' + convert(varchar, @PageSize) + ' as PageSize,0 as TotalCount'
set @SQL1 = @SQL1 + ' return end'

set @SQL1 = @SQL1 + ' set @PageCount=(@TotalCount+' + convert(varchar, @PageSize) + '-1)/' + convert(varchar, @PageSize)    --获取总页数
set @SQL1 = @SQL1 + ' set @PageIndex=' + convert(varchar, @PageIndex)    --设置正确的页索引
set @SQL1 = @SQL1 + ' if @PageIndex0 set @PageIndex=1'
set @SQL1 = @SQL1 + ' if @PageIndex@PageCount and @PageCount0 set @PageIndex=@PageCount'
set @SQL1 = @SQL1 + ' set @StartRow=(@PageIndex-1)*' + convert(varchar, @PageSize) + '+1'

if (charindex(',', @OrderBy)=0 and charindex(@PK, @OrderBy)0)
begin
    --****************************************************************************
    --****************不需要创建主键********************************************
    --****************************************************************************
    declare @SortDirection varchar(10)    --排序方向,=:升序,=:倒序
    set @SortDirection = '='
    if charindex('desc', @OrderBy)  0
        set @SortDirection = '='
    set @SQL2 = @SQL2 + ' declare @Sort varchar(100)'    --声明一个变量,用来记录当前页第一条记录的排序字段值
    set @SQL2 = @SQL2 + ' set rowcount @StartRow'    --设置返回记录数截止到当前页的第一条
    set @SQL2 = @SQL2 + ' select @Sort=' + @PK + ' from ' + @Tables + @Where + ' order by ' + @OrderBy    --获取当前页第一个排序字段值
    set @SQL2 = @SQL2 + ' set rowcount ' + convert(varchar, @PageSize)    --设置返回记录数为页大小
    set @Where = @Where + ' and ' + @PK + @SortDirection + '@Sort'
    set @SQL2 = @SQL2 + ' select ' + @Fields + ' from ' + @Tables + @Where + ' order by ' + @OrderBy    --输出最终显示结果
end
else
begin
    --****************************************************************************
    --*************需要创建自增长主键******************************************
    --****************************************************************************
    set @SQL2 = @SQL2 + ' declare @EndRow int'
    set @SQL2 = @SQL2 + ' set @EndRow=@PageIndex*' + convert(varchar, @PageSize)
    set @SQL2 = @SQL2 + ' set rowcount @EndRow'
    set @SQL2 = @SQL2 + ' declare @PKBegin int'    --声明一个变量,开始索引
    set @SQL2 = @SQL2 + ' declare @PKEnd int'    --声明一个变量,结束索引
    set @SQL2 = @SQL2 + ' set @PKBegin=@StartRow'
    set @SQL2 = @SQL2 + ' set @PKEnd=@EndRow'
    --****************************************************************************
    --************对特殊字段进行转换,以便可以插入到临时表******************
    --****************************************************************************
    declare @TempFields varchar(500)
    set @TempFields=@Fields
    set @TempFields = replace(@TempFields, ''''' as CheckBox', '')
    set @TempFields = replace(@TempFields, ''''' as DetailButton', '')
    set @TempFields = replace(@TempFields, ''''' as Radio', '')
    set @TempFields = LTRIM(RTRIM(@TempFields))
    if left(@TempFields,1)=','    --去除最左边的逗号
        set @TempFields = substring(@TempFields, 2, len(@TempFields))
    if right(@TempFields,1)=','    --去除最右边的逗号
        set @TempFields = substring(@TempFields, 1, len(@TempFields)-1)

    set @SQL2 = @SQL2 + ' select identity(int,1,1) as PK,' + @TempFields + ' into #tb from ' + @Tables + @Where + ' order by ' + @OrderBy
    --****************************************************************************
    --********去除字段的表名前缀,当有字段有别名时,只保留字段别名*********
    --****************************************************************************
    declare @TotalFields varchar(500)
    declare @tmp varchar(50)
    declare @i int
    declare @j int
    declare @iLeft int --左括号的个数
    declare @iRight int --右括号的个数
    set @i = 0
    set @j = 0
    set @iLeft = 0
    set @iRight = 0
    set @tmp = ''
    set @TotalFields = ''

    while (len(@Fields)0)
    begin
        set @i = charindex(',', @Fields)

        --去除字段的表名前缀
        if (@i=0)
        begin
            --找不到逗号分割,即表示只剩下最后一个字段
            set @tmp = @Fields
        end
        else
        begin
            set @tmp = substring(@Fields, 1, @i)
        end
        set @j = charindex('.', @tmp)
        if (@j0)
            set @tmp = substring(@tmp, @j+1, len(@tmp))
        --*******当有字段有别名时,只保留字段别名*********

        --带括号的情况要单独处理,如Convert(varchar(10), B.EndDate, 120) as EndDate
        while (charindex('(', @tmp)  0)
        begin
            set @iLeft = @iLeft + 1
            set @tmp = substring(@tmp, charindex('(', @tmp)+1, Len(@tmp))
        end
        while (charindex(')', @tmp)  0)
        begin
            set @iRight = @iRight + 1
            set @tmp = substring(@tmp, charindex(')', @tmp)+1, Len(@tmp))
        end

        --当括号恰好组队的时候,才能进行字段别名的处理
        if (@iLeft = @iRight)
        begin
            set @iLeft = 0
            set @iRight = 0
            --不对这几个特殊字段作处理:CheckBox、DetailButton、Radio
            if (charindex('CheckBox', @tmp) = 0 and charindex('DetailButton', @tmp) = 0 and charindex('Radio', @tmp) = 0)
            begin
                --判断是否有别名
                if (charindex('as', @tmp)  0)--别名的第一种写法,带'as'的格式
                begin
                    set @tmp = substring(@tmp, charindex('as', @tmp)+2, len(@tmp))
                end
                else
                begin
                    if (charindex(' ', @tmp)  0)--别名的第二种写法,带空格(" ")的格式
                    begin
                        while(charindex(' ', @tmp)  0)
                        begin
                            set @tmp = substring(@tmp, charindex(' ', @tmp)+1, len(@tmp))
                        end
                    end
                end
            end
            set @TotalFields = @TotalFields + @tmp
        end
        if (@i=0)
            set @Fields = ''
        else
            set @Fields = substring(@Fields, @i+1, len(@Fields))

    end
    --print @TotalFields

    set @SQL2 = @SQL2 + ' select ' + @TotalFields + ' from #tb where PK between @PKBegin and @PKEnd order by PK'    --输出最终显示结果
    set @SQL2 = @SQL2 + ' drop table #tb'
end

--输出“PageIndex(页索引)、PageCount(页数)、PageSize(页大小)、TotalCount(总记录数)”
set @SQL2 = @SQL2 + ' select @PageIndex as PageIndex,@PageCount as PageCount,' 
                + convert(varchar, @PageSize) + ' as PageSize,@TotalCount as TotalCount'

--print @SQL1 + @SQL2
exec(@SQL1 + @SQL2)
如果使用这个通用分页存储过程的话,那么调用方法如下:
使用通用分页存储过程进行分页
/**//*
============================================================
功能:    获取销售目标,根据条件
参数:
    @UserType int,
    @OrgID varchar(500),
    @TargetPeriodBegin nvarchar(50),
    @TargetPeriodEnd nvarchar(50),
    @BranchOrgCode nvarchar(50),
    @BranchOrgName nvarchar(50),
    @OrgCode nvarchar(50),
    @OrgName nvarchar(50),
    @SalesCode nvarchar(50),
    @SalesName nvarchar(50),
    @CatalogCode nvarchar(50),
    @CatalogName nvarchar(50),
    @PageIndex int,                当前要显示的页的页索引,索引从1开始,无记录时为0。
    @PageSize int,                页大小
创建者:Hollis Yao
创建日期:2006-08-11
备注:
============================================================
*/
Create PROCEDURE [dbo].[GetSalesTargetList] 
@UserType int,
@OrgID nvarchar(500),
@TargetPeriodBegin nvarchar(50),
@TargetPeriodEnd nvarchar(50),
@BranchOrgCode nvarchar(50),
@BranchOrgName nvarchar(50),
@OrgCode nvarchar(50),
@OrgName nvarchar(50),
@SalesCode nvarchar(50),
@SalesName nvarchar(50),
@CatalogCode nvarchar(50),
@CatalogName nvarchar(50),
@PageIndex int,
@PageSize int
AS
declare @Condition nvarchar(2000)
set @Condition = ''
if (@UserType1)
    set @Condition = @Condition + ' and A.OrgID in (' + @OrgID + ')'
if (len(@TargetPeriodBegin)0)
    set @Condition = @Condition + ' and A.TargetPeriod =''' + @TargetPeriodBegin + ''''
if (len(@TargetPeriodEnd)0)
    set @Condition = @Condition + ' and A.TargetPeriod =''' + @TargetPeriodEnd + ''''
if (len(@BranchOrgCode)0)
    set @Condition = @Condition + ' and F.OrgCode like ''%' + @BranchOrgCode + '%'''
if (len(@BranchOrgName)0)
    set @Condition = @Condition + ' and F.OrgName like ''%' + @BranchOrgName + '%'''
if (len(@OrgCode)0)
    set @Condition = @Condition + ' and E.OrgCode like ''%' + @OrgCode + '%'''
if (len(@OrgName)0)
    set @Condition = @Condition + ' and E.OrgName like ''%' + @OrgName + '%'''
if (len(@SalesCode)0)
    set @Condition = @Condition + ' and C.SalesCode like ''%' + @SalesCode + '%'''
if (len(@SalesName)0)
    set @Condition = @Condition + ' and C.SalesName like ''%' + @SalesName + '%'''
if (len(@CatalogCode)0)
    set @Condition = @Condition + ' and D.CatalogCode like ''%' + @CatalogCode + '%'''
if (len(@CatalogName)0)
    set @Condition = @Condition + ' and D.CatalogName like ''%' + @CatalogName + '%'''
if (len(@Condition)0)
    set @Condition = substring(@Condition,5,len(@Condition))
--print @Condition
exec sp_Paging
    N'',N''' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10), B.BeginDate, 120) as BeginDate, Convert(varchar(10), B.EndDate, 120) as EndDate,
        C.SalesCode, C.SalesName, D.CatalogCode, D.CatalogName, E.OrgID, E.OrgName, F.OrgID as BranchOrgID, F.OrgCode as BranchOrgCode, F.OrgName as BranchOrgName, A.Amount, '' as DetailButton',
    N'ChlSalesTarget as A
    left outer join ChlSalesTargetPeriod as B on A.TargetPeriod=B.TargetPeriod
    left outer join ChlSales as C on A.Sales=C.SalesCode
    left outer join ChlItemCatalog as D on A.ItemCatalog=D.CatalogCode
    left outer join ChlOrg as E on A.OrgID=E.OrgID
    left outer join ChlOrg as F on C.BranchOrgID=F.OrgID',
    @Condition,
    N'A.TargetPeriod desc,C.SalesName,D.CatalogName',
    @PageIndex, @PageSize

来源:http://www.tulaoshi.com/n/20160219/1598677.html

延伸阅读
标签: Web开发
建立一个 Web 应用,分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。但这种分页方法仅适用于较小数据量的情形,因为游标本身有缺点:游标是存放在内存中,很费内存。游标一建立,就将相关的记录锁住,直到取消游标...
标签: ASP
  /*****听以前的同事说asp页面上的分页太慢了(如果数据多了), 就想了这么个笨办法。有些地方还要考虑----比如select top 22 * from cat_list where T_id not in (select T_id from #change)是否有效率问题;数据不能重复等等 不过灵活性挺好。希望各位高手再给帮忙改正;多谢chair3的帮助---这个存储过程还可以在加入几个变量,随便...
其实在很多时候设计的度还是要把握的,不至于让自己陷入的怪圈中才是最重要的,因为我们还要留出时间还解决其他的很多问题,个人认为适度就可以了,留出一定的空间。也因为万能是不存在的,万物在一定的范畴之内都是合理的,出了范畴可能就没有合理的了。          分页存储过程大致有下列几种 1、&n...
代码如下: create database Test on primary ( name='Test_Data.mdf', filename='D:\我的资料\sql\备份\Test_Data.mdf' ) log on ( name='Test_Data.ldf', filename='D:\我的资料\sql\备份\Test_Data.ldf' ) if object_id('tb') is not null drop table tb create table tb ( Col int ) insert into tb select top 50 number ...
标签: ASP
  在  网上 讨论 如何 实现 分页  有很多程序,我在这里向大家  介绍一种实现分页的新的方法,使用 存储过程 来实现分页    由于 这段程序写的 比较早,那个时候 还没有 SQL 7,每一个 Varchar 只能 支持 255 个字符,所以 采取了一种比较笨的办法,如果大家有兴趣,请去  http:/...

经验教程

828

收藏

27
微博分享 QQ分享 QQ空间 手机页面 收藏网站 回到头部