SQL Error handling

SQL Server is a great tool, but when you are handling large volumes of queries that run automatically, you need a good framework wrapped around each query to ensure that if something out of the ordinary happens, you not only know about it, but it does not adversely imact your data.

Below is the an excerpt from our SQL Server wiki

Critical Errors can cause unexpected results within a transaction. Actions taken before the critical error will be rolled back and the transaction closed. Remaining actions will be performed without a transaction wrappper.

To overcome this we have developed a template for scripts that provide a reasonable expectation of all or nothing output for scripted updates.

This template is recommended for changes to existing schema particularly if data is present in the table being modified.

The template consists of a generic header and footer for the transaction handling. Each command is followed by a generic code block to ensure a transaction remains in effect.


-----------------------------------------------------------------
-- STANDARD ERROR HANDLING - HEADER START
-----------------------------------------------------------------
IF EXISTS (SELECT *
FROM tempdb..sysobjects
WHERE id = Object_id('tempdb..#tmpErrors'))
DROP TABLE #tmperrors

GO

CREATE TABLE #tmperrors (
error INT)

GO

SET xact_abort on

GO

SET TRANSACTION isolation LEVEL serializable

GO

BEGIN TRANSACTION

GO

-----------------------------------------------------------------
-- STANDARD ERROR HANDLING - HEADER END
-----------------------------------------------------------------

-- INSERT YOUR FIRST STATEMENT HERE
GO

IF @@ERROR <> 0
AND @@TRANCOUNT > 0
ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT = 0
BEGIN
INSERT INTO #tmperrors
(error)
SELECT 1

BEGIN TRANSACTION
END

GO

-- INSERT YOUR SECOND STATEMENT HERE AND REPEAT THE BELOW BLOCK AFTER EACH SUBSEQUENT STATEMENT
GO

IF @@ERROR <> 0
AND @@TRANCOUNT > 0
ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT = 0
BEGIN
INSERT INTO #tmperrors
(error)
SELECT 1

BEGIN TRANSACTION
END

GO

-----------------------------------------------------------------
-- STANDARD ERROR HANDLING - FOOTER START
-----------------------------------------------------------------
IF EXISTS (SELECT *
FROM #tmperrors)
ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT > 0
BEGIN
PRINT 'The database update succeeded'

COMMIT TRANSACTION
END
ELSE
PRINT 'The database update failed'

GO

DROP TABLE #tmperrors

GO

IF @@TRANCOUNT > 0
BEGIN
PRINT 'CRITICAL PROBLEM - TRANSACTION STILL OPEN - ' + CONVERT(VARCHAR,@@TRANCOUNT) + ' STILL ACTIVE'
END

GO

-----------------------------------------------------------------
-- STANDARD ERROR HANDLING - FOOTER END
-----------------------------------------------------------------