sql server 2000 a missing "commit" 1

[duchovnick]

I wrote a procedure as follows:

/*EXEC complete_month_magazin v9*/
/*DROP PROC complete_month_magazin*/

CREATE PROCEDURE complete_month_magazin @table1 VARCHAR(10)
AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
  BEGIN
    IF EXISTS (SELECT * FROM sysobjects WHERE name=@table1)
      PRINT 'a'   
    ELSE
      PRINT 'b'
      Return
  END
COMMIT
GO

If @table1 exists i get 'a' as expected. If it doesnt exist i get an error message:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 3, current count = 4.

I put a second "commit" at every corner of that short procedure and cannot get rid of that error.
Could you please help me with that ?

 

[bborissov]

/*EXEC complete_month_magazin v9*/
/*DROP PROC complete_month_magazin*/

CREATE PROCEDURE complete_month_magazin @table1 VARCHAR(10)
AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
  BEGIN
    IF EXISTS (SELECT * FROM sysobjects WHERE name=@table1)
      PRINT 'a'   
    ELSE
      PRINT 'b'
[COLOR=red]
      ROLLBACK TRANSACTION
[/color]
      Return
  END
COMMIT
GO

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

I forgot, you must have either COMMIT either ROLLBACK before each RETURN in your SP.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

Thanks a lot Borislav.
Now it works fine !