求 解读一个存储过程
0
<h2>求 解读一个存储过程</h2>
<div id="postmessage_264657" class="t_msgfont"><strong>只从语法解读即可：</strong><br /><br /><br />/*清单支付月报*/ CREATE PROCEDURE P_JL_QingDanYueBao @ContractID varchar(20),@Chapter varchar(20),@PeriodID varchar(20)<br />AS<br />set nocount on<br />declare @QS int<br />select @QS=PeriodCode from Jl_Period where [email=ID=@PeriodID]ID=@PeriodID[/email]<br />create Table #ls_yb (<br />ID varchar(20),<br />ItemCode varchar(20),--细目编号<br />ItemName varchar(150),--细目名称<br />Unit varchar(20),--单位<br />UnitPrice Decimal(18,4),--合同单价<br />Amount Decimal(18,8),--合同数量<br />AffChgAmount Decimal(18,8),--变更后合同数量<br />TotalMoney Decimal(18,4),-- 合同金额<br />ThisPeriodAmount Decimal(18,8),--本期完成量<br />PrePeriodTotalAmount Decimal(18,8),--上期累计完成量<br />PrePeriodTotalMoney Decimal(18,4),--上期累计完成金额<br />ThisPeriodMoney Decimal(18,4),-- 本期完成金额<br />ThisPeriodTotalAmount Decimal(18,8),-- 本期累计完成量<br />ThisPeriodTotalMoney Decimal(18,4),-- 本期累计完成金额<br />FinishRate Decimal(18,4) -- 完成率<br />)<br />insert into #ls_yb<br />SELECT ID,ItemCode,ItemName,Unit,UnitPrice,Amount,0,Round(TotalMoney,0),0,0,0,0,0,0,0<br />from JL_ContractList <br />WHERE ContractID = @ContractID and [email=Chapter=@Chapter]Chapter=@Chapter[/email] <br /><br />update #ls_yb set AffChgAmount=a.Amount from<br />( SELECT ID,Amount<br />from JL_ContractList <br />WHERE ContractID = @ContractID and [email=Chapter=@Chapter]Chapter=@Chapter[/email]) a <br />where #ls_yb.ID=a.ID<br /><br />update #ls_yb set AffChgAmount=a.AffChgAmount from<br />( SELECT AffChgAmount,ItemCode<br />from Jl_ListChgCheck <br />WHERE ContractID = @ContractID) a <br />where #ls_yb.ItemCode=a.ItemCode<br /><br /><br /><br />update #ls_yb set PrePeriodTotalAmount=a.OrderThisTimeFinish from<br />(select ContractItemID,sum(OrderThisTimeFinish) as OrderThisTimeFinish from V_JL_ContractList_ListComput where [email=ContractID=@ContractID]ContractID=@ContractID[/email] and [email=Chapter=@Chapter]Chapter=@Chapter[/email] and PeriodCode<@QS Group by ContractItemID ) a <br />where #ls_yb.ID=a.ContractItemID<br />update #ls_yb set ThisPeriodTotalAmount=a.OrderThisTimeFinish from<br />(select ContractItemID,sum(OrderThisTimeFinish) as OrderThisTimeFinish from V_JL_ContractList_ListComput where [email=ContractID=@ContractID]ContractID=@ContractID[/email] and [email=Chapter=@Chapter]Chapter=@Chapter[/email] and PeriodCode<[email==@QS]=@QS[/email] Group by ContractItemID ) a <br />where #ls_yb.ID=a.ContractItemID <br /><br />update #ls_yb set ThisPeriodTotalAmount =0 where ThisPeriodTotalAmount is NULL<br />update #ls_yb set PrePeriodTotalAmount =0 where PrePeriodTotalAmount is null<br />update #ls_yb set ThisPeriodAmount = ThisPeriodTotalAmount-PrePeriodTotalAmount<br /><br />update #ls_yb set PrePeriodTotalMoney = round(PrePeriodTotalAmount * UnitPrice,2), <br />ThisPeriodMoney =round(ThisPeriodAmount * UnitPrice,2), <br />ThisPeriodTotalMoney = round(ThisPeriodTotalAmount * UnitPrice,2)<br /><br />update #ls_yb set FinishRate = Round(ThisPeriodTotalAmount / Amount * 100, 2) where Amount>0<br />update #ls_yb set ThisPeriodTotalMoney =0 where ThisPeriodTotalMoney is NULL<br />update #ls_yb set PrePeriodTotalMoney =0 where PrePeriodTotalMoney is null<br />update #ls_yb set ThisPeriodMoney = ThisPeriodTotalMoney-PrePeriodTotalMoney<br />select * from #ls_yb order by ItemCode<br />drop table #ls_yb<br />set nocount off<br />GO</div>
