May 1, 2013 at 6:11 am
I had a problem when transfering an active application and to a loan table where i have to calculate the monthly payment.
When i try to execute the stored procedure i get this error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Here is the code use in the procedure. When i executed is says commited succesfully. So i dont know why i get this message. I tried several things i seen on google but this getting this annoying message. I will very thankfull if someone can help me with this.
ALTER Procedure [dbo].[Transfer_ApprovedApp_intoLoans]
@FirstPaymentDate as Date
AS
Begin
Begin Transaction
DECLARE @RowCountAsInt;
DECLARE @ApplicationIDas char(5);
DECLARE @LoanTypeIDaschar(5);
DECLARE @LoanAmountAs Numeric(8,4);
DECLARE @LoanRateAs Numeric(12,8);
DECLARE @LoanYears As Int;
DECLARE @NumberOfMonthsAs Int = 12; /**Number of months**/
DECLARE @MonthlyRateAs Numeric(12,8); /**To use to convert the anual rate to monthly rate **/
DECLARE @NumberOfPeriodsAsInt; /**Months multiplied by the number of years**/
DECLARE @MonthlyPaymentAsNumeric(8,4);
DECLARE @RateinPercentAS Integer = 100
DECLARE @statusAS Char(1)
/**Check if application is active and approved**/
SELECT *
FROM Applications
WHERE ApplicationID = @ApplicationID AND Status = 'A'
/**Check if Loan exists**/
SELECT @RowCount = Count(*)
FROM Applications
WHERE ApplicationID = @ApplicationID
IF @RowCount = 0
BEGIN
PRINT 'Applicación' + @ApplicationID + ' no está aprobada o activa';
Return;
END;
Else
/**Declare cursor**/
Declare AppTransferLoanCursor CURSOR for
Select ApplicationId,LoanTypeId,LoanAmount,LoanRate,LoanYears, [Status]
From Applications
Where Status = 'A' and Transferred = 'N';
/** Open Cursor **/
Open AppTransferLoanCursor
Fetch Next from AppTransferLoanCursor Into @ApplicationID, @LoanTypeID, @LoanAmount,
@LoanRate, @LoanYears, @status
While @@FETCH_STATUS = 0
Begin
/**Calculate monthly payment**/
Set @MonthlyRate =(@LoanRate/@RateinPercent)/@NumberOfMonths
Set @NumberOfPeriods = @LoanYears* @NumberOfMonths
Set @MonthlyPayment=@LoanAmount/(power(1+@LoanRate,@NumberOfPeriods)-1)*(@LoanRate*power(1+@LoanRate,@NumberOfPeriods))
/*Mark the application as transferred**/
Update Applications
Set Transferred = 'Y'
Where ApplicationId = @ApplicationId
/**Transfer values into Loans**/
Insert into Loans(LoanType,LoanAmount,LoanRate,LoanYears,MonthlyPayment,Status)
Values(@LoanTypeID,@LoanAmount,@LoanRate,@LoanYears,@MonthlyPayment, @status)
/**To fetch next row**/
Fetch Next from AppTransferLoanCursor Into @ApplicationID, @LoanTypeID, @LoanAmount,
@LoanRate, @LoanYears, @status
End
Commit Transaction
Close ApplicationTransferCursor
DEALLOCATE ApplicationTransferCursor
End
May 1, 2013 at 6:28 am
...
/**Check if Loan exists**/
SELECT @RowCount = Count(*)
FROM Applications
WHERE ApplicationID = @ApplicationID
IF @RowCount = 0
BEGIN
PRINT 'Applicación' + @ApplicationID + ' no está aprobada o activa';
ROLLBACK TRANSACTION -- complete the transaction before exiting the stored procedure
Return;
END;
Else
...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 1, 2013 at 6:46 am
You should consider replacing the slow slow cursor with a fast set-based equivalent:
UPDATE a SET
Transferred = 'Y'
OUTPUT
a.LoanTypeId,
a.LoanAmount,
a.LoanRate,
a.LoanYears,
MonthlyPayment = a.LoanAmount/(y.Result-1) * (a.LoanRate*y.Result),
a.[Status]
INTO Loans
FROM Applications a
CROSS APPLY (SELECT NumberOfPeriods = a.LoanYears * @NumberOfMonths) x
CROSS APPLY (SELECT Result = power(1+a.LoanRate,x.NumberOfPeriods)) y
WHERE a.[Status] = 'A'
AND a.Transferred = 'N';
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply