Error handling using TRY and CATCH in T-SQL

T-SQL has keywords TRY and CATCH that can be use to handle errors and in particular to work out when you should COMMIT a transation and when you should ROLLBACK.

The usage pattern in a stored procedure goes like this:

CREATE PROCEDURE spThingamajig @DooDahId int
AS
/* spThingamajig
 * Do some stuff to the thingamajig of the given DooDahId
 *
 * Created 2011-06-14 by rwb.
 */
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
			-- Temporary table, to show how to clean it up.
			
			CREATE TABLE #tThingies (intId int NOT NULL PRIMARY KEY)
			
			INSERT INTO #tThingies (intId)
			SELECT intId
			FROM tblThingamajig
			WHERE intDooDahId = @DooDahId
			
			-- Do stuff with #tThingies.
			
			DROP TABLE #tThingies
			
			-- Do more stuff.
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@Trancount > 0
		BEGIN
			ROLLBACK TRANSACTION
		END
		EXEC usp_RethrowError -- Logs exception to a table and RAISERROR to notify the caller.
	END CATCH
	
	-- Finally, we make certain that our temporary tables disappear.
	IF( object_id('tempdb..#tThingies') ) IS NOT NULL
		DROP TABLE #tThingies
END

The TRANSACTION lives inside the TRY. If the exception is thrown on a statement inside the TRANSACTON then there will be an uncommitted transaction that we must roll back. An exception could also occur while trying to commit the transaction, in which case it may have already rolled back and so @@Trancount = 0. Hence the check is necessary.

The temporary table may not be dropped inside the TRY if an exception occurs before the DROP statement. Therefore we must check at the end of the proceudre that the table has been cleared up.

Home | More stuff | Octad of the week