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!