Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror:

  • 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

  • ...

    /**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

    ...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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';

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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