毕业后接触的第一个存储过程就是分页了,原理是建立临时表,然后往里插数据,利用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)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @SQL=' WHERE(1=1) '
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF @entryID<>0
SET @SQL=@SQL+' AND(ProjectID=@entryID) '
ELSE
SET @SQL=@SQL+' AND(ProjectID in (select SubentryID from Subentrys where SStatus=0)) '
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF @type ='3'
SET @SQL=@SQL+''
ELSE
SET @SQL=@SQL+' AND(Type= @type) '
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF @status='0'
SET @SQL=@SQL+' AND(Status!=6) '
ELSE IF @status<>'7'
Set @SQL=@SQL+' AND(Status=@status) '
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @countSql = N'
select @rowCount=count(id)
from Cheque a left outer join BankAccounts b on a.PayAccountID = b.BankAccID
'
+@SQl
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
EXEC sp_executesql @countSql,
N'
@entryID int,
@type char,
@status char,
@rowCount int output
',
@entryID,
@type,
@status,
@rowCount output
select @recordCount=@rowCount
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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))
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**
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
**/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--set @pageCount=ceiling(@recordCount*1.0/@pageSize)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**
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