Mai jos am creat o procedura ce calculeaza si afiseaza un desfasurator de dobanda bancara.

Procedura are urmatorii parametri:

Procedura va afisa urmatoarele coloane:

Anul – anul pt care se face simularea

SoldulInitial – soldul de la inceputul anului

Luna1 – suma depusa + dobanda la sfarsitul primei luni (daca este capitalizare lunara) sau suma depusa (daca este capitalizare anuala). (se pleaca de la premiza ca depunerea de suma se face in prima zi a fiecarei luni)

Luna2  pana la Luna12 – similar cu Luna1

Economie – totalul sumelor depuse pe anul respectiv

Dobanda – dobanda primita in anul respectiv

SoldFinal – soldul la sfarsitul anului respectiv

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— exec [BD_CalculDobanda] 100, 10, 2.45, 0
— =============================================
ALTER PROCEDURE [dbo].[BD_CalculDobanda]

@SumaLunara money,
@NrAni int,
@ProcentDobanda float,
@CapitalizareAnual int
AS
BEGIN

SET NOCOUNT ON;
declare @Anul int
declare @Economie money
declare @Dobanda money
declare @SoldFinal money
declare @SoldInitial money
select @SoldFinal = 0,@SoldInitial =0
create table #tempDobanda
(Anul int, SoldInitial money, Luna1 money,Luna2 money, Luna3 money, Luna4 money, Luna5 money, Luna6 money,
Luna7 money, Luna8 money, Luna9 money, Luna10 money, Luna11 money, Luna12 money,Economie money, Dobanda money, SoldFinal money)

declare @i int
set @i=@NrAni
while(@i>0)
begin
select @Anul=@NrAni-@i+1
if @CapitalizareAnual = 1
begin
select @Economie = 12*@SumaLunara
select @SoldFinal = @SoldFinal+ @Economie
select @Dobanda = @SoldFinal*@ProcentDobanda/100
select @SoldFinal=@SoldFinal + @Dobanda
insert into #tempDobanda(Anul,SoldInitial,Economie,Dobanda,SoldFinal) values (@Anul,@SoldInitial,@Economie,@Dobanda,@SoldFinal)
select @SoldInitial = @SoldFinal
end
— CAPITALIZARE
if @CapitalizareAnual = 0
begin
declare @Luna1 money,@Luna2 money,@Luna3 money,@Luna4 money,@Luna5 money,@Luna6 money,
@Luna7 money,@Luna8 money,@Luna9 money,@Luna10 money,@Luna11 money,@Luna12 money

select @Luna1=@SumaLunara+@SoldInitial+(@SumaLunara+@SoldInitial)*@ProcentDobanda/100 / 12
select @Luna2=(@Luna1+@SumaLunara)*@ProcentDobanda/100 / 12 + @SumaLunara +@Luna1
select @Luna3=(@Luna2+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna2
select @Luna4=(@Luna3+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna3
select @Luna5=(@Luna4+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna4
select @Luna6=(@Luna5+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna5
select @Luna7=(@Luna6+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna6
select @Luna8=(@Luna7+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna7
select @Luna9=(@Luna8+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna8
select @Luna10=(@Luna9+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna9
select @Luna11=(@Luna10+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna10
select @Luna12=(@Luna11+@SumaLunara)*@ProcentDobanda/100 / 12+ @SumaLunara +@Luna11
select @Economie=@SumaLunara*12
select @Dobanda = @Luna12-@Economie*@Anul
select @SoldFinal = @Luna12
insert into #tempDobanda
values (
@Anul,@SoldInitial,
@Luna1,@Luna2,@Luna3,@Luna4,@Luna5,@Luna6,
@Luna7,@Luna8,@Luna9,@Luna10,@Luna11,@Luna12,
@Economie,@Dobanda,@SoldFinal
)
select @SoldInitial=@SoldFinal
end

set @i=@i-1
end
if @CapitalizareAnual = 1
begin
update #tempDobanda set Luna1=@SumaLunara, Luna2=@SumaLunara,Luna3=@SumaLunara,Luna4=@SumaLunara,Luna5=@SumaLunara,
Luna6=@SumaLunara,Luna7=@SumaLunara, Luna8=@SumaLunara,Luna9=@SumaLunara,Luna10=@SumaLunara,Luna11=@SumaLunara,Luna12=@SumaLunara
end
select * from #tempDobanda
END