472,127 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

ROLLBACK ALTER column statement


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:

Expand|Select|Wrap|Line Numbers
  3. ALTER TABLE dbo.tbl_name
  4. ADD  [column1] [varchar] (20) NULL 
  5. -----------------------------------------------
  7. --Error handling
  8. IF @@Error = 0  
  9.     Begin
  11.         print '-Success'   
  12.     END
  13. ELSE
  14.     Begin
  16.         print '-Failed'
  17.     End
  19. GO
Thanks in advance for the help!
Apr 2 '08 #1
2 10871
2,878 Expert 2GB
Try putting the t-sql you want monitored in a dynamic query.

Something like:
Expand|Select|Wrap|Line Numbers
  2. set @sqlStatement = 'ALTER SOMETHING'
  4. exec (@sqlStatement)
  6. if @@error <> 0
  7.    ROLLBACK
  8. else 
  9.   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
Apr 2 '08 #2
Thank you for the help on this!
Apr 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
7 posts views Thread by Serge Rielau | last post: by
3 posts views Thread by Gregor Kovač | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.