Stored Procedures don’t necessarily stop at an error

I normally expect precedence to save me in my stored procedures. If I have six statements and the third ones fails, I expect the fourth through sixth not to run. Boy, was I wrong. Normally I test my stored procedures a step at a time and don’t end up seeing this kind of behavior, but I now have stored procedures running that occasionally are fed bad data and that’s how I discovered this problem. I’m using SQL Server 2008R2, so I don’t know if this holds true for older versions, but I think it likely does. Fortunately, SQL Server 2008 has a solution.

The stored procedure that revealed this error to me is one of the ones I use to import data into our warehouse daily. It takes the data from a table where it has been accumulating from the previous day and moves it to an interim table. The data is then copied from the interim table to the permanent table, but is left in this interim table so the previous day’s load can be more easily referenced. The problem comes from the truncates. Once the data is copied to the interim table, the accumulation table is truncated to prepare for the next day’s data. The interim table is truncated at the beginning of the process to remove the data we’d been storing for reference.

The other night, I was alerted to an error in the load. There was a primary key violation on this table. I went to see what the key violation was, but the accumulation table had already been truncated. I thought for a moment that perhaps there just was no data and was wondering why I’d gotten a primary key violation error. After all, the truncate came after the insert. If the insert had failed, surely the truncate shouldn’t have happened. I did some testing and found that no, the truncate had indeed happened and I needed to make some changes to prevent this from happening in the future. (Fortunately, the data was easily replicated so I didn’t lose anything)

I’ll be using my Country table for this example. I created three versions of the table for this test: StartCountry, InterimCountry and EndCountry. StartCountry simulates the origin or accumulation table, InterimCountry the interim table and EndCountry the permanent table.

CREATE TABLE StartCountry(
CountryAlpha char(2) NOT NULL,
LongAlpha char(3) NULL,
NumberCode char(3) NULL,
CountryName varchar(45) NULL
)

CREATE TABLE InterimCountry(
CountryAlpha char(2) NOT NULL,
LongAlpha char(3) NULL,
NumberCode char(3) NULL,
CountryName varchar(45) NULL,
PRIMARY KEY CLUSTERED (CountryAlpha ASC)
)

CREATE TABLE EndCountry(
CountryAlpha char(2) NOT NULL,
LongAlpha char(3) NULL,
NumberCode char(3) NULL,
CountryName varchar(45) NULL
)

To simulate the error, you’ll notice there’s only a Primary Key on the Interim table. In the actual database, there’s one on the permanent table as well, I just didn’t bother to add one here.

This is the stored procedure that I had been using

TRUNCATE TABLE InterimCountry

INSERT INTO InterimCountry(CountryAlpha, LongAlpha, NumberCode, CountryName)
SELECT CountryAlpha, LongAlpha, NumberCode, CountryName
FROM StartCountry

TRUNCATE TABLE StartCountry

INSERT INTO EndCountry(CountryAlpha, LongAlpha, NumberCode, CountryName)
SELECT CountryAlpha, LongAlpha, NumberCode, CountryName
FROM InterimCountry

Nice and simple, no input parameters, doesn’t return anything, just moves some data and ends. Unfortunately, even though the error happened in the first INSERT INTO, the second TRUNCATE still ran. I had thought it would stop when the error happened, but it kept going.

I created the primary key conflict by running the following query twice

INSERT INTO StartCountry(CountryAlpha, LongAlpha, NumberCode, CountryName)
SELECT CountryAlpha, LongAlpha, NumberCode, CountryName
FROM Country

I ran the query and sure enough, no data in any of the three tables. I ran the query twice again and prepared my new stored procedure. This one uses TRY…CATCH. All my stored procedures probably should use TRY…CATCH, but old habits are hard to break.

I have two main inserts in my stored procedure, so I’m using two TRY…CATCH blocks, each with its own TRANSACTION block.

TRUNCATE TABLE InterimCountry

BEGIN TRY
      BEGIN TRANSACTION
      INSERT INTO InterimCountry(CountryAlpha, LongAlpha, NumberCode, CountryName)
      SELECT CountryAlpha, LongAlpha, NumberCode, CountryName
      FROM StartCountry

      COMMIT TRANSACTION

END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
      RETURN
END CATCH

TRUNCATE TABLE StartCountry

BEGIN TRY
      BEGIN TRANSACTION
      INSERT INTO EndCountry(CountryAlpha, LongAlpha, NumberCode, CountryName)
      SELECT CountryAlpha, LongAlpha, NumberCode, CountryName
      FROM InterimCountry
      COMMIT TRANSACTION

END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
      RETURN
END CATCH

Now this works as intended. I ran the stored procedure with the problem in the primary key and it stopped before truncating the source table. I ran it without the primary key violation and it truncated where it was supposed to and populated where it was supposed to. I was surprised to see my original stored procedure fail in the way that it did, but I’m very happy that SQL Server 2008 has a way to solve that problem without elaborate work-arounds.

MS BOL Using Try…Catch in T-SQL
MS BOL Try…Catch

This entry was posted in 2008, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>