Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror: Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 7:12 AM
Points: 1, Visits: 2
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 @RowCount As Int;
DECLARE @ApplicationID as char(5);
DECLARE @LoanTypeID as char(5);
DECLARE @LoanAmount As Numeric(8,4);
DECLARE @LoanRate As Numeric(12,8);
DECLARE @LoanYears As Int;
DECLARE @NumberOfMonths As Int = 12; /**Number of months**/
DECLARE @MonthlyRate As Numeric(12,8); /**To use to convert the anual rate to monthly rate **/
DECLARE @NumberOfPeriods As Int; /**Months multiplied by the number of years**/
DECLARE @MonthlyPayment As Numeric(8,4);
DECLARE @RateinPercent AS Integer = 100
DECLARE @Status AS 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

Post #1448381
Posted Wednesday, May 1, 2013 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
...
/**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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448388
Posted Wednesday, May 1, 2013 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse