Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count

I am getting this exception about commits and rollbacks but am not sure what exactly is wrong with my Stored Procedure. I have read the answers in other such questions and am unable to find where exactly the commit count is getting messed up. So, this is the Stored Procedure I use: ``` -- this is a procedure used for the purge utility. This procedure uses the parameters of a date and lets user select -- if the leads that should be purge must be closed either before, on or since that date. -- operator: 0-->less 1-->equal 2-->greater -- @closed: closing date -- leadscount: returns the count of leads deleted IF OBJECT_ID ('LEAD_PURGE', 'P') IS NOT NULL DROP PROCEDURE LEAD_PURGE go CREATE PROCEDURE LEAD_PURGE @purgextns INT, @leadscount INT OUTPUT AS BEGIN BEGIN TRANSACTION CREATE TABLE #ASSIGNMENTS_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) CREATE TABLE #MAPRESULTS_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) CREATE TABLE #COMMAND_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) CREATE TABLE #PROGRESS_STATUS_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) CREATE TABLE #DETAILS_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) CREATE TABLE #NEEDS_DELETED ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) insert into #ASSIGNMENTS_DELETED select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD); SELECT @leadscount = (SELECT COUNT(*) FROM PURGE_LEAD); INSERT INTO #MAPRESULTS_DELETED SELECT ID FROM MAPRESULT WHERE ASSIGNMENTSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED) INSERT INTO #COMMAND_DELETED SELECT ID FROM EXECUTERULECOMMAND WHERE MAPRESULTID IN (SELECT ID FROM #MAPRESULTS_DELETED) INSERT INTO #PROGRESS_STATUS_DELETED SELECT PROGRESS_STATUS_ID FROM COMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED) INSERT INTO #DETAILS_DELETED SELECT DETAILID FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD) INSERT INTO #NEEDS_DELETED SELECT NEEDSID FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD) DELETE FROM PROGRESS_STATUS WHERE ID IN (SELECT ID FROM #PROGRESS_STATUS_DELETED) DELETE FROM EXECUTERULECOMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED) DELETE FROM COMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED) DELETE FROM SIMPLECONDITIONAL WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED) DELETE FROM MAPPREDICATE WHERE ROWBP IN (SELECT ID FROM MAPROW WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED)) DELETE FROM MAPROW WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED) DELETE FROM MAPRESULT WHERE ID IN (SELECT ID FROM #MAPRESULTS_DELETED) DELETE FROM ASSIGNMENTATTACHMENTS WHERE ASSIGNMENTSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED) DELETE FROM LEADOBSERVER WHERE ASSIGNSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED) DELETE FROM MAPDESTINATIONS WHERE SUGGESTEDASSIGNID IN (SELECT ID FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM #ASSIGNMENTS_DELETED)) DELETE FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM #ASSIGNMENTS_DELETED) DELETE FROM PRODUCTINTEREST WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD) CREATE TABLE #SALE_DELETED_EX ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) INSERT into #SALE_DELETED_EX SELECT SALEEXSEQ FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD)) DELETE FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD)) DELETE FROM SALEEXTENSIONS WHERE SEQID IN (SELECT ID FROM #SALE_DELETED_EX) DELETE FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD) DELETE FROM NOTES WHERE OBJECTID IN (SELECT ID FROM #NEEDS_DELETED) OR OBJECTID IN (SELECT ID FROM #DETAILS_DELETED) DELETE FROM HISTORYRECORD WHERE OBJECTID IN (SELECT ID FROM #DETAILS_DELETED) DELETE FROM DETAIL WHERE SEQID IN (SELECT ID FROM #NEEDS_DELETED UNION SELECT ID FROM #DETAILS_DELETED) DELETE FROM MESSAGES WHERE PROVIDERID IN (SELECT ID FROM PURGE_LEAD) DELETE FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD) DELETE FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD) CREATE TABLE #PURGE_LEAD_E ( ID NUMERIC(19, 0) PRIMARY KEY (ID) ) INSERT into #PURGE_LEAD_E Select SEQID FROM LEADEXTENSIONS WHERE SEQID NOT IN (SELECT LEADEXSEQ FROM LEAD) if @purgextns = 1 begin DELETE FROM LEADEXTENSIONS WHERE SEQID IN (SELECT ID FROM PURGE_LEAD_E) end DELETE FROM PURGE_LEAD; DROP TABLE #ASSIGNMENTS_DELETED DROP TABLE #MAPRESULTS_DELETED DROP TABLE #COMMAND_DELETED DROP TABLE #PROGRESS_STATUS_DELETED DROP TABLE #DETAILS_DELETED DROP TABLE #NEEDS_DELETED DROP TABLE #PURGE_LEAD_E DROP TABLE #SALE_DELETED_EX COMMIT END go ``` now I call this procedure in the following code: ``` try { c = new ConnectionHelper().getConnection(); String sql = ""; if (shouldPurgeExtns) { progressModel.makeProgress("progress.deleting.dependents"); purgeMultiselect(c, LEAD, isMSSQL); } sql = "{CALL " + TOPLinkManager.getSchemaPrefix() + "LEAD_PURGE (?,?)}"; cs = c.prepareCall(sql); cs.setInt(1, shouldPurgeExtns ? 0 : 1); cs.registerOutParameter(2, java.sql.Types.INTEGER); cs.executeUpdate(); int rowcount = cs.getInt(2); cs.close(); progressModel.makeProgress("progress.recording.history"); recordHistory(c, isMSSQL, LEAD, DateTypeDecorator.CLOSED, date, rowcount); done(progressModel); c.close(); return true; } catch (Exception e) { Logs.main.error("Error Purging Leads", e); throw new Exception(e.getMessage()); } ``` And I get an exception on the line which say `int rowcount = cs.getInt(2);` The Exception is: ``` com.microsoft.sqlserver.jdbc.SQLServerException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454) at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults(SQLServerStatement.java:1083) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getOutParameter(SQLServerCallableStatement.java:112) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getterGetParam(SQLServerCallableStatement.java:387) ``` Please help me out. at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue(SQLServerCallableStatement.java:393) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getInt(SQLServerCallableStatement.java:437) at marketsoft.tools.purge.PurgeUtils.PurgeLeads(PurgeUtils.java:283) EDIT: as I have answered this question myself... I would like to change the question a bit now. Why was no exception thrown in the execute method???