SQL_实现分页功能存储过程

 

毕业后接触的第一个存储过程就是分页了，原理是建立临时表，然后往里插数据，利用select top 语句来查询最后返回的可以分页数据。

ALTER PROCEDURE dbo.JE_Get_ChequeBySelectItem
 @pageSize int, 
 @pageIndex int, 
 @pageCount int output,
 @recordCount int output ,
 @userID int,
 @entryID int,
 @type char,
 @status char(2)

AS
Begin
declare @ID int
declare @SQL nvarchar(3000)
declare @CStatus nvarchar(255)
declare @PaySysMoney decimal(18,2)
declare @currentMoney decimal(18,2)
declare @rowCount int
declare @countSql nvarchar(3000)

SET @SQL=' WHERE(1=1) '

IF  @entryID<>0
SET @SQL=@SQL+' AND(ProjectID=@entryID) '
ELSE 
SET @SQL=@SQL+' AND(ProjectID in (select SubentryID from Subentrys where SStatus=0)) '

IF @type ='3'
SET @SQL=@SQL+''
ELSE 
SET @SQL=@SQL+' AND(Type= @type) '

IF @status='0'
SET  @SQL=@SQL+' AND(Status!=6) '
ELSE IF @status<>'7'
Set @SQL=@SQL+' AND(Status=@status) '

set @countSql = N'
select @rowCount=count(id)
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID 
'
+@SQl

EXEC sp_executesql @countSql,
N'
 @entryID int,
 @type char,
 @status char,
 @rowCount int output
',
  @entryID,
@type,
@status,
@rowCount output
 
 select @recordCount=@rowCount

 CREATE TABLE #A(id int,ChequeNo int,Total decimal(18,2),UseType varchar(50),Receiver varchar(150),CreateTime datetime,HasInvoice char(2),ProjectID int,Type char(1),Status varchar(255), payer varchar(255),AgencyDeptment varchar(500),InputPerson  varchar(255),Descript varchar(500),CStatus varchar(255))

 set @pageCount=ceiling(@rowCount*1.0/@pageSize)
 
if @pageIndex = 0 or @pageCount<=1
begin
set @SQL =@SQL+' ORDER BY ID DESC'
SET @SQL=N'
SELECT top '+str(@pageSize)+' id ,CAST(ChequeNo as int)  ChequeNo,Total,UseType,Receiver,CreateTime, HasInvoice,ProjectID,Type,Status ,b.BankAccName as payer ,AgencyDeptment,InputPerson,a.Descript
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID 
'
+@SQL

INSERT INTO #A(id,ChequeNo,Total,UseType,Receiver,CreateTime,HasInvoice,ProjectID,Type,Status,payer,AgencyDeptment,InputPerson,Descript)
EXEC sp_executesql @SQL,
N'
 @entryID int,
 @type char,
 @status char,
 @pageSize int 
',
  @entryID,
@type,
 @status,
 @pageSize
end

 else if @pageIndex = @pageCount -1
 begin
 set @SQL =@SQL+' ORDER BY ID ASC'
 SET @SQL=N'
select * from (SELECT top '+str((@recordCount - @pageSize * @pageIndex))+' id ,CAST(ChequeNo as int)  ChequeNo,Total,UseType,Receiver,CreateTime, HasInvoice,ProjectID,Type,Status ,b.BankAccName as payer ,AgencyDeptment,InputPerson,a.Descript
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID 
'
+@SQL+') TempTable order by id desc '
INSERT INTO #A(id,ChequeNo,Total,UseType,Receiver,CreateTime,HasInvoice,ProjectID,Type,Status,payer,AgencyDeptment,InputPerson,Descript)
EXEC sp_executesql @SQL,
N'
 @entryID int,
 @type char,
 @status char,
 @pageSize int,
 @pageIndex int  
',
  @entryID,
@type,
 @status,
 @pageSize,
 @pageIndex
end

 else
 begin
 set @SQL =@SQL+' ORDER BY ID ASC'
 SET @SQL=N'
select top '+str(@pageSize) +' *  from(SELECT top '+str(@recordCount - @pageSize * @pageIndex)+' id ,CAST(ChequeNo as int)  ChequeNo,Total,UseType,Receiver,CreateTime, HasInvoice,ProjectID,Type,Status ,b.BankAccName as payer ,AgencyDeptment,InputPerson,a.Descript
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID 
'
+@SQL+')TempTable order by id desc'
INSERT INTO #A(id,ChequeNo,Total,UseType,Receiver,CreateTime,HasInvoice,ProjectID,Type,Status,payer,AgencyDeptment,InputPerson,Descript)
EXEC sp_executesql @SQL,
N'
 @entryID int,
 @type char,
 @status char
',
  @entryID,
@type,
 @status
 end


/**//**
SET @SQL=N'
SELECT id ,CAST(ChequeNo as int)  ChequeNo,Total,UseType,Receiver,CreateTime, HasInvoice,ProjectID,Type,Status ,b.BankAccName as payer ,AgencyDeptment,InputPerson,a.Descript
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID 
'
+@SQL
**/


 
 set @CStatus =' '
 
 select top 1  @PaySysMoney=PaySysMoney from PaySysSetting --需要处长审核的金额
 
 declare CrsrVar Cursor for select id,Status,Total from #A
 
 Open CrsrVar
 
fetch next from CrsrVar into @ID,@CStatus,@currentMoney

 while (@@FETCH_STATUS=0)
 begin
 
 if @CStatus='0'
  begin
  set @CStatus ='状态错误'
  end
 else if  @CStatus='1'
 begin
 set @CStatus ='未审核'
 end
 else if @CStatus='2'
 begin
  set @CStatus =(select  top 1 RealName from Users ,AuditAttitudes where Users.UserID=AuditAttitudes.AuditID and AuditAttitudes.ChequeID=@ID order by AuditAttitudeID desc )
  set @CStatus=@CStatus+'通过<br>待处长审核'
 end
 else if @CStatus='3'
 begin
set @CStatus =(select top 1 RealName from Users ,AuditAttitudes where Users.UserID=AuditAttitudes.AuditID and AuditAttitudes.ChequeID=@ID and Users.AuditLevel=2 order by AuditAttitudeID desc )
  set @CStatus=@CStatus+'通过'
  if(@currentMoney>=@PaySysMoney)
   begin
   if exists (select RealName from Users ,AuditAttitudes where Users.UserID=AuditAttitudes.AuditID and AuditAttitudes.ChequeID=@ID and Users.AuditLevel=3 )
   begin 
   set @CStatus=@CStatus+'<br>'+(select top 1 RealName  from Users ,AuditAttitudes where Users.UserID=AuditAttitudes.AuditID and AuditAttitudes.ChequeID=@ID and Users.AuditLevel=3 order by AuditAttitudeID desc )+'通过'
   end
   end
 end
 else if @CStatus='4'
 begin
  set @CStatus ='<font color=red>未通过</font>'
 end
 else if @CStatus='5'
 begin
  set @CStatus ='已作废'
 end 
 else if @CStatus='6'
 begin
  set @CStatus ='已删除'
 end
 
 update #A set CStatus=@CStatus where id =@ID
 
 fetch next from CrsrVar into @ID,@CStatus,@currentMoney
 end 
 close CrsrVar
 deallocate CrsrVar
 
 
 
--select @recordCount=count(*) from #A

--set @pageCount=ceiling(@recordCount*1.0/@pageSize)
/**//**
if @pageIndex = 0 or @pageCount<=1
set @SQL='select top '+str(@pageSize)+' * from #A order by id asc'
else if @pageIndex = @pageCount -1
 set @SQL='select * from ( select top '+str(@recordCount - @pageSize * @pageIndex)+' * from #A order by id desc) TempTable order by id asc '
else  set @SQL='select top '+str(@pageSize) +' *  from ( select top '+str(@recordCount - @pageSize * @pageIndex)+' * from #A order by id desc) TempTable order by id asc' 
exec(@SQL)
**/
select * from #A
drop table #A
end