Home T-SQL Transaction syntax
Reply: 0

T-SQL Transaction syntax

user1048
1#
user1048 Published in May 28, 2018, 3:17 am

I'm implementing a mission critical stored procedure that will perform UPDATE, DELETE and INSERT and I want to make sure my TRANSACTION is correctly formed.

I've seen some TRANSACTION statements where there's a check after each step. I've also seen this kind where the entire set of steps are simply placed within a singleTRANSACTION block without any "check points" along the way.

Is this a well formed TRANSACTION that will roll back everything i.e. UPDATE, DELETE and INSERT, if there's any error at any point.

Here's the TRANSACTION:

BEGIN TRANSACTION

BEGIN TRY

   UPDATE SomeTable
   SET SomeColumnValue = 123
   WHERE Id = 123456

   DELETE FROM SomeOtherTable
   WHERE Id = 789

   INSERT INTO ThirdTable
      (Column1, Column2)
   VALUE
      ('Hello World', 1234567)  

END TRY
BEGIN CATCH

   ROLLBACK TRANSACTION

END CATCH
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.314247 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO