Else statement bug or transaction effect ?

Question

Hi all,

I'm using the following code in sql server 2005.

BEGIN TRANSACTION;
CREATE TABLE dbo.MyTable
(
 idLang int NOT NULL IDENTITY (1, 1),
 codeLang nvarchar(4) NOT NULL
)  ON [PRIMARY];

IF @@ERROR = 0
 BEGIN
  PRINT 'before_commit';
  COMMIT TRANSACTION;
  PRINT 'after_commit';
 END
ELSE
 BEGIN
  PRINT 'before_rollback';
  ROLLBACK TRANSACTION;
  PRINT 'after_rollback';
 END
GO

1 - Display when MyTable doesn't exist (no error case) :
before_commit
after_commit

=> OK

2 - Display when MyTable exists (error case) :
'There is already an object named 'MyTable' in the database.'

=> Why the "else" statement is not executed ? (no print, no rollback)

I know the alternative with try-catch but i'd like to understand this strange case...

Thanks !

Answer 1

I can't say specifically why your problem is occurring. Personally, I'm not sure I would use a transaction and error handling or a try/catch block to do this.

Have you tried querying the sys.tables table instead to check for its existence. Something of this ilk:

IF EXISTS(SELECT * FROM sys.tables WHERE object_id = object_id('MyTable'))
BEGIN
 print 'table already exists'
END
ELSE
BEGIN
    CREATE TABLE dbo.MyTable
    (
        idLang int NOT NULL IDENTITY (1, 1),
        codeLang nvarchar(4) NOT NULL
    )  ON [PRIMARY];
END

Answer 2

The CREATE TABLE will be checked during query compilation and fail, so none of the code in the batch is executed. Try adding:

SELECT @@TRANCOUNT

To the end of the script (i.e. after the GO), and you'll see the BEGIN TRANSACTION never occurred either.

Answer 3

Damien_The_Unbeliever > I understand. Thank you.
James Wiseman > This "Create table" was just a test case but I agree with you.