title: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count
body: <p>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.</p>
  
  <p>So, this is the Stored Procedure I use:</p>
  
  <pre><code>-- 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--&gt;less 1--&gt;equal 2--&gt;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
  </code></pre>
  
  <p>now I call this procedure in the following code:</p>
  
  <pre><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());
      }
  </code></pre>
  
  <p>And I get an exception on the line which say <code>int rowcount = cs.getInt(2);</code></p>
  
  <p>The Exception is:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>as I have answered this question myself... I would like to change the question a bit now.</p>
  
  <p><strong>Why was no exception thrown in the execute method???</strong></p>