用友U8分页存储过程

ALTER procedure   [dbo].[wa_sp_PRGetRecordsPaginationByPsn]

--参数 

@TableName   nvarchar (2000),     --数据源表 

@Order   nvarchar (2000) =   '' ,    --排序 

@Fields   nvarchar (4000) =   '*' ,    --显示字段 

@Condition   nvarchar (4000) =   '1=1' ,   --查询条件 

@Key   nvarchar (100),     --主键字段,可以是复合主键（用逗号隔开） 

@PageSize   int   = 30,     --页大小 

@PageCurrent   int   = 1   output ,    --欲显示页，也作为返回值 

       /* 

         If 没数据 then @PageCurrent=1 

                                     else 有数据     

                                      If @PageCurrent=0，取所有页；@PageCount不变 

                                           ElseIf @PageCurrent > 总PageCount，返回最后一页      

       */ 

@PageCount   int output ,     --总页数，作为返回值 

@PaginationKey   nvarchar (200) =   '1' ,   --分页用的主键

@PaginationHaving   nvarchar (2000) =   ''

     

as 

--存储体 

set nocount on 

SET   ANSI_WARNINGS   off 

 

declare   @i   int   , @j   int ,@RecordCount   int 

/*guopeng 变量不够长 拆成4个*/ 

declare   @ss   nvarchar   (4000) 

declare   @sss   nvarchar   (4000) 

declare   @ssss   nvarchar   (4000) 

declare   @sssss   nvarchar   (4000) 

declare   @ssssss   nvarchar   (4000)

declare   @tbname   nvarchar   (44)

set   @tbname =   '##PRTB'   +   replace (   newid (), '-'   , '_' )

/*生成临时表*/ 

set   @ss =   'select 0 AS ID_galAxylcw, '   + @PaginationKey +   ' as KeyField into '   + @tbname +   ' from '   + @TableName  

set   @sss=   ' where '   + @Condition +   ' Group by '   + @PaginationKey +   ','   +   replace ( replace   (@Order, 'desc' ,   '' ), 'asc'   , '' ) +   ' having '   + @PaginationHaving +   ' order by '   + @Order  

exec   (@ss+@sss)

 

/*满足条件的记录个数*/ 

declare   @Field   nvarchar   (2000)

set   @Field =   ' distinct '   + @PaginationKey

declare   @sql   nvarchar   (100)

set   @sql =   'select @i = count(0) from '   + @tbname

exec    sp_executesql   @sql,N '@i      int      output'   ,@i    output   

set   @RecordCount = @i 

/*得到总页数，注意使用convert先转换整型为浮点型，防止小数部分丢失*/ 

set   @PageCount =   ceiling   ( convert (   float ,@i)/@PageSize) 

 

/*调整正确的显示页码，仅当不时显示所有页时才调整*/ 

if   @PageCurrent <> 0    --不全显示 

                   if   @PageCount = 0    --没有数据 

                                   set   @PageCurrent = 1 

                   else      --有数据 

                                   if   @PageCurrent > @PageCount 

                                                   set   @PageCurrent = @PageCount 

 

/*返回参数已设置完成，现在返回数据集*/      

if    @PageCurrent = 0   --要求返回所有记录 

                   exec ( 'select '   + @Fields +   ' from '   + @TableName +   ' where '   + @Condition +   ' order by '   + @O rder) 

else      --返回指定页 

                   begin 

                                   /*@i保存开始记录序号（序号从1开始）*/ 

                                   set   @i = (@PageCurrent-1) * @PageSize + 1 

                                   /*@j保存结束记录序号（序号从1开始）*/ 

                                   set   @j = @i + @PageSize - 1   --因为使用Between @i and @j，所以， 

                                    --即使最后一页也不用求实际记录结束序号 

                                   /*生成临时表，只取主键字段(合为一个字段)。该表按@Order排序，生成标志列*/ 

                                   /*主键合为一个字段*/ 

                                   set   @Key =   replace   (@Key,   ',' ,   '+' ) 

                                   /*返回结果集*/ 

                                   set   @ssssss =   ' declare @i int;set @i = 0;update '   + @tbname +   ' set @i=@i+1,ID_galAxylcw = @i; '

                                   set   @ssss = ' select '   + @Fields  

                                   set   @sssss =   ' from '   + @TableName +   ' where '   + @Condition +   ' and '   + @PaginationKey +   ' in (select KeyField from '   + @tbname +   ' where ID_galAxylcw between '   +  

                                    cast (@i   as nvarchar   (20)) +   ' and '   +   cast (@j   as nvarchar   (20)) +   ') order by '   + @Order 

                                   exec (@ssssss + @ssss + @sssss) 

                   end 

exec   ( 'drop table '   + @tbname)

set nocount off 

SET   ANSI_WARNINGS   on 

 

return   @RecordCount

使用：

[dbo].[wa_sp_PRGetRecordsPaginationByPsn] 'Customer_info','id' , '*','id>14000' ,'id', 20,1 ,1, 'id','sum(id)>10'

declare @i int , @j int,@RecordCount int

select @i = count( 0) from Customer_info

set @PageCount = ceiling (convert( float, @i)/@PageSize )