ROLLBACK ALTER column statement

[NarutoFanatic]

Hello,

I'm having trouble using a transaction - rollback statement in sql. I can't seem to have the rollback executed during an error when i've altered a table to add a column with the same name (run the alter script twice on purpose to test the rollback). Somehow i got an error of the column should be unique (since the column already added during the 1st execution of the script). I want the sql execution to display the Failed instead and rollback the transaction. My code is as follows:

BEGIN TRANSACTION
 
ALTER TABLE dbo.tbl_name
ADD  [column1] [varchar] (20) NULL 
-----------------------------------------------
 
--Error handling
IF @@Error = 0  
    Begin
        COMMIT TRANSACTION
        print '-Success'   
    END
ELSE
    Begin
        ROLLBACK TRANSACTION
        print '-Failed'
    End
 
GO
 
 

Thanks in advance for the help!

[ck9663]

Try putting the t-sql you want monitored in a dynamic query.

Something like:

BEGIN TRANS
set @sqlStatement = 'ALTER SOMETHING'
 
exec (@sqlStatement)
 
if @@error <> 0
   ROLLBACK
else 
  COMMIT

Also, there's nothing to rollback since the command did not execute. You might also want to check which error can be trapped in relation to it's severity.

-- CK

[NarutoFanatic]

Thank you for the help on this!