SQL通用分页存储过程
程序员文章站
2022-06-26 14:34:13
...
ZT. http://hi.baidu.com/lecherline/blog/item/8feebe5145959d2542a75beb.html
好久没有上来写点东西了，今天正好有空，共享一些个人心得，就是关于分页的存储过程，这个问题应该是老生重谈了，网上的通用存储过程的类型已经够多了，但是，好象看到的基本上不能够满足一些复杂的SQL语句的分页（也可能是我不够见多识广啊，呵呵），比如下面这句
上面这句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
备注：
*/
如果使用这个通用分页存储过程的话，那么调用方法如下：
使用通用分页存储过程进行分页
/**//*
功能： 获取销售目标，根据条件
参数：
@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 (@UserType<>1)
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
好久没有上来写点东西了，今天正好有空，共享一些个人心得，就是关于分页的存储过程，这个问题应该是老生重谈了，网上的通用存储过程的类型已经够多了，但是，好象看到的基本上不能够满足一些复杂的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 1<>1'
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 @PageIndex<0 set @PageIndex=1'
set @SQL1 = @SQL1 + ' if @PageIndex>@PageCount and @PageCount>0
set @[email protected]'
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 @[email protected]*' + 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 @[email protected]'
set @SQL2 = @SQL2 + ' set @[email protected]'
--****************************************************************************
--************对特殊字段进行转换，以便可以插入到临时表******************
--****************************************************************************
declare @TempFields varchar(500)
set @[email protected]
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)
--去除字段的表名前缀 本篇文章发表于www.xker.com(小新技术网)
if (@i=0)
begin
--找不到逗号分割，即表示只剩下最后一个字段
set @tmp = @Fields
end
else
begin
set @tmp = substring(@Fields, 1, @i)
end
set @j = charindex('.', @tmp)
if (@j>0)
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 (@UserType<>1)
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