When I first came across SQL Server’s Try/Catch implementation back in 2005, one of the first things I tested was how transactions worked within them and whether or not both the try and catch blocks created implied transactions.

If you have no interest in any detail and you’re just after a quick answer, the answer is, No.  SQL Server Try/Catch blocks do not create implicit transactions as a result of their use.

If you didn’t want the details but now you’re thinking ‘Prove it’ then please read on.

So this is a pretty easy one to prove.  We will start by creating a Try/Catch and a couple of statements in the try:

CREATE TABLE #temp (Value varchar(30))

BEGIN TRY
 INSERT INTO #temp
 VALUES ('If you see me, this worked');
 SELECT 1/0 AS ThisWillFail;
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH

SELECT Value FROM #temp
DROP TABLE #temp

Upon running this, you will see that the last Select from the temporary table returns the inserted value and the divide by zero Select failed.  This proves that statements within a Try block, behave in the same was they would outside of one; in that each statement creates its own implicit transaction.

For completeness’ sake, if you want an all or nothing Try block containing multiple statements you would need to do something like this

CREATE TABLE #temp (Value varchar(30))

BEGIN TRY
BEGIN TRANSACTION
    INSERT INTO #temp
    VALUES ('If you see me, this worked');
    SELECT 1/0 AS ThisWillFail;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    ROLLBACK TRANSACTION;
END CATCH

SELECT Value FROM #temp
DROP TABLE #temp

In this example, the two statements are enclosed in an explicit transaction that gets rolled back if the catch block is executed.  It is worth emphasising the need to roll the transaction back if it fails.

Advertisements