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.