Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE procMortgage
-- Add the parameters for the stored procedure here
@PropertyVal decimal(15,2),
@LoanAmount decimal(15,2),
@Term int,
@InterestRate float
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #MORT (paymentNo int, StartBalance decimal(15,2), Payment decimal(15,2),Interest decimal(15,2), EndBalance decimal(15,2))
DECLARE @Payment decimal(15,2);
DECLARE @R float;
SET @R = @InterestRate / 12 / 100.0;
DECLARE @N int;
SET @N = 12 * @Term;
SET @PAYMENT = ceiling(@R / (1 - POWER(1 + @r , -@N)) * @LoanAmount * 100) / 100;
SELECT @r, @PAYMENT ,@R / (1 - POWER(1 + @r , -@N));
DECLARE @C int;
SET @C = 1
DECLARE @SBAL DECimal(15,2);
DECLARE @EBAL DECimal(15,2);
DECLARE @INTEREST DECIMAL(15,2);
SET @SBAL = @LoanAmount;
WHILE @C <= @N
BEGIN
SELECT @INTEREST = @SBAL * @R;
SELECT @EBAL = @SBAL + @INTEREST - @PAYMENT;
--CREATE TABLE #MORT (paymentNo int, StartBalance decimal(15,2), Payment decimal(15,2),Interest decimal(15,2), EndBalance decimal(15,2))
INSERT #MORT VALUES(@C, @SBAL, @PAYMENT, @INTEREST, @EBAL);
SET @SBAL = @EBAL;
SET @C = @C + 1;
END
SELECT * FROM #MORT ORDER BY paymentNo;
END
GO
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.