update multiple table with transaction between try catch in sql server

I need to create script for entire database changes every week for my client to run on production server, when I was creating the script I need to check whatever I have created so I try to use transaction and to roll back every change, so I used try catch but it inserted many records in some master tables while there was some error in script and still error was not catching by try catch where i have written rollback transaction.

In this article we will see different ways what we can try and what is the best way to achieve our target.

Let's say we added some new columns to different tables and added some new values in our master table so first and some procedure changes, let's see what we think first

Begin Transaction
Begin Try    
   ALTER TABLE TableX ADD column1  varchar(50)
   ALTER TABLE TableY ADD column2  char(2)

   INSERT some_master (col1, col2, active) 
   VALUES (N'aaaaa', N'bbbbbb', 1)

   INSERT [dbo].[Lookup_Analyst] (col1, col2, active) 
   VALUES (N'xxxxxx', N'yyyyy', 1)


   -- Rollback    
   Rollback Transaction
End Try
Begin Catch
   Rollback Transaction
End Catch

Suppose last insert statement creates some error, say referential constraints or length of the column or any other issue, what will happen, It looks it will catch by try catch block and whatever changes we made in table structure and other insert statement will be rolled back, right?

No, table structure will be changes as well as other inserted records will not be rolled back

According to Microsoft:

There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:

• Compile errors, such as syntax errors that prevent a batch from executing.
• Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

So it is clear, we cannot use try catch to test our script as we discussed in the beginning of this article, so how we can test our script, we will use SET XACT_ABORT ON to catch the error and rollback

Here is the complete code to test our script

SET XACT_ABORT ON
BEGIN TRANSACTION    
   ALTER TABLE TableX ADD column1  varchar(50)
   ALTER TABLE TableY ADD column2  char(2)

   INSERT some_master (col1, col2, active) 
   VALUES (N'aaaaa', N'bbbbbb', 1)

   INSERT [dbo].[Lookup_Analyst] (col1, col2, active) 
   VALUES (N'xxxxxx', N'yyyyy', 1)


   -- Rollback    
   Rollback Transaction

It will give error if there would be any and other changes will be rolled back

Suppose we tested and and now we want to commit the records then we can change our rollback to commit, that's it.

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,084

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 924

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,311

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 666

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,556