Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

  • Hey all...

    I'm a convert from Oracle PL\SQL and I know I'm missing something in my transaction control in this statement, but I can't seem to figure out what.  I've played with placement of BEGIN and END statements around the IF @@ERROR <> 0 statements, but I can't seem to resolve this error.

    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

    I typically get a previous count that is one less than the current count. 

    My code is.

    CREATE PROCEDURE sp_ORLInsertDefect

     @ProjectNum int,

     @Description varchar(100),

     @Packager tinyint,

     @Severity varchar(25),

     @Testcase varchar(50),

     @Detail varchar(8000)

    AS

     DECLARE @currTime datetime, @NewDefID int

     SELECT @NewDefID = max(defect_id) FROM DEFECTS

     SET @NewDefID = @NewDefID + 1 

     SET @currTime = GetDate()

     BEGIN TRAN T1

     INSERT INTO defects(defect_id, project_id, defect_sub_date, sev_no, procedure_type,

       description, status, pkgr_id) VALUES (@NewDefID, @ProjectNum, @currTime, @Severity, @TestCase,

       @Description, 'OPEN', @Packager)

     IF @@ERROR <> 0

      ROLLBACK TRANSACTION T1

      SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'

      RETURN(99)

     INSERT INTO test_detail(defect_id, data) VALUES (@NewDefID, @Detail)

     IF @@ERROR <> 0

      ROLLBACK TRANSACTION T1

      SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'

      RETURN(99)

     COMMIT TRAN T1

    GO

    Any help would be greatly appreciated.  Thanks!!

  • After IF @@ERROR 0 in each case you have multiple statements - you need to surround these by BEGIN and END. As it works just now if there are no errors you're transaction will run :

    IF @@ERROR 0

    ROLLBACK TRANSACTION T1 -- this doesn't run but execution jumps to outside now

    -- so these two lines will run and the proc will return without rolling back.

    SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'

    RETURN(99)

    Dave Hilditch.

  •  "SELECT @NewDefID = max(defect_id) FROM DEFECTS

     SET @NewDefID = @NewDefID + 1 "

    As and aside, you could set defect_id as an IDENTITY field with a Seed of 1 (the default) .

    This will have the effect of automatically populating the defect_id with the next available number (and therefore you will not have to calculate or pass it). 

    Look at "Creating and Modifiying Identity Columns" and "Autonumbering and Indentifier Columns" in SQL Books Online.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply