{"id":1019,"date":"2010-10-28T00:00:00","date_gmt":"2010-10-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/defensive-error-handling\/"},"modified":"2021-09-29T16:21:56","modified_gmt":"2021-09-29T16:21:56","slug":"defensive-error-handling","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/defensive-error-handling\/","title":{"rendered":"Defensive Error Handling"},"content":{"rendered":"
\n

The ability to handle errors is essential in any programming language and, naturally, we have to implement safe error handling in our T-SQL if we want to build solid SQL Server code. SQL Server 2005, and later, superseded the old style @@Error<\/span> error handling, with the TRY…CATCH<\/span> blocks that are more familiar to Java and C# programmers.<\/p>\n

While use of TRY…CATCH<\/span> certainly is the best way to handle errors in T-SQL, it is not without difficulties. Error handling in T-SQL can be very complex, and its behavior can sometimes seem erratic and inconsistent. Furthermore, error handling in Transact SQL lacks many features that developers who use languages such as Java and C# take for granted. For example, in SQL Server 2005 and 2008, we cannot even re-throw an error without changing its error code. This complicates handling errors, because we have to write separate conditions for detecting exceptions caught for the first time, and for detecting re-thrown exceptions.<\/p>\n

This article will demonstrate simple SQL Server error handling, using XACT_ABORT<\/span> and transactions, describe the most common problems with TRY…CATCH<\/span> error handling, and advocate that the defensive programmer, where possible, implements only simple error checking and handling in SQL Server, with client-side error handling used to enforce what is done on the server.<\/p>\n

Prepare for Unanticipated Failure<\/h1>\n

Any statement can, and at some point inevitably will, fail. This may seem to be a statement of the obvious, but too many programmers seem to assume that once their code “works” then the data modifications and queries that it contains will always succeed.<\/p>\n

In fact, data modifications can and do fail unexpectedly. For example, the data may not validate against a constraint or a trigger, or the command may become a deadlock victim. Even if the table does not have any constraints or triggers at the time the code is developed, they may be added later. It is wise to assume that our modifications will not always succeed.<\/p>\n

Many queries, too, can fail. Just as a modification can become a deadlock victim, so can a SELECT<\/span> (unless that SELECT<\/span> is running under either of the two snapshot isolation levels). If a SELECT<\/span> statement utilizes a user-defined function, then errors may occur in that function that will cause the query to fail. Other common causes of failure are queries that attempt to use a temporary table that does not exist, or contain subqueries that return more than one value.<\/p>\n

Listing 1-1 demonstrates a very simple case of a SELECT<\/span> statement that may succeed or fail depending on locale settings.<\/p>\n

CREATE VIEW dbo.NextNewYearEve AS \nSELECT DATEADD\n        (YEAR,\n         DATEDIFF(year, '12\/31\/2000', CURRENT_TIMESTAMP),\n         '12\/31\/2000'\n        ) AS NextNewYearEve ; \nGO \n\nSET LANGUAGE us_english ; \nSELECT  NextNewYearEve\nFROM    dbo.NextNewYearEve ; \n\nChanged language setting to us_english.\nNextNewYearEve\n-----------------------\n2009-12-31 00:00:00.000\n\nSET LANGUAGE Norwegian ; \nSELECT  NextNewYearEve\nFROM    dbo.NextNewYearEve ; \nGO \n\nChanged language setting to Norsk.\nNextNewYearEve\n-----------------------\nMsg 241, Level 16, State 1, Line 2\nConversion failed when converting date and\/or time from character string.\n\nDROP VIEW dbo.NextNewYearEve ;  \n<\/pre>\n
Listing 1-1: Language settings can cause certain date queries to fail<\/p>\n
The main point is clear: when we develop T-SQL code, we cannot assume that our queries or data modifications will always succeed and we need to be prepared for such failures and handle them gracefully. When an unexpected error occurs during data modification, it is essential that execution of the statement is terminated, the database is returned to the state it was in before the statement started, and a message is sent to the calling client, giving some details of the error that occurred and the action taken as a result. Likewise, if a SELECT<\/span> fails that is part of a longer transaction that has already modified data then these modifications must be undone as well.<\/p>\n
Using Transactions for Data Modifications<\/h1>\n
In many cases, during data modifications, we cannot take our database from one consistent state to another in a single statement. If a data modification requires more than one statement to effect the required change, then explicit transactions should be used to ensure that these statements succeed or fail as a unit, and that our error handling can handle them as a unit.<\/p>\n
For example, suppose that we need to log in one table all the modifications made to another table. Listing 1-2 shows the code to create the table to be modified (Codes<\/span>) and the table in which the modifications will be logged (CodeDescriptionsChangeLog<\/span>).<\/p>\n
IF EXISTS ( SELECT  *\n            FROM    INFORMATION_SCHEMA.TABLES\n            WHERE   TABLE_NAME = 'Codes'\n            AND TABLE_SCHEMA = 'dbo' )\n  BEGIN;\n    -- we used a Codes table in a previous chapter\n    -- let us make sure that is does not exist any more\n    DROP TABLE dbo.Codes ;\n  END ;\nGO\nCREATE TABLE dbo.Codes\n  (\n    Code VARCHAR(10) NOT NULL ,\n    Description VARCHAR(40) NULL ,\n    CONSTRAINT PK_Codes PRIMARY KEY CLUSTERED ( Code )\n  ) ; \nGO\n\n-- we did not use this table name before in this book,\n-- so there is no need to check if it already exists\nCREATE TABLE dbo.CodeDescriptionsChangeLog\n    (\n      Code VARCHAR(10) NOT NULL ,\n      ChangeDate DATETIME NOT NULL ,\n      OldDescription VARCHAR(40) NULL ,\n      NewDescription VARCHAR(40) NULL ,\n      CONSTRAINT PK_CodeDescriptionsChangeLog PRIMARY KEY ( Code, ChangeDate )\n    ) ;\n<\/pre>\n
Listing 1-2: The Codes and <\/span>CodeDescriptionsChangeLog tables<\/span><\/p>\n
Note that the log table does not have a FOREIGN<\/span> KEY<\/span> constraint referring to the Codes<\/span> table, because the log records need to be kept even if we delete the corresponding rows in Codes<\/span>.<\/p>\n
The procedure shown in Listing 1-3 modifies the Codes<\/span> table, and logs the change in the CodeDescriptionsChangeLog table.<\/span><\/p>\n
CREATE PROCEDURE dbo.ChangeCodeDescription\n    @Code VARCHAR(10) ,\n    @Description VARCHAR(40)\nAS \n    BEGIN ; \n        INSERT  INTO dbo.CodeDescriptionsChangeLog\n                ( Code ,\n                  ChangeDate ,\n                  OldDescription ,\n                  NewDescription\n                )\n                SELECT  Code ,\n                        CURRENT_TIMESTAMP ,\n                        Description ,\n                        @Description\n                FROM    dbo.Codes\n                WHERE   Code = @Code ; \n\n        UPDATE  dbo.Codes\n        SET     Description = @Description\n        WHERE   Code = @Code ; \n    END ;  \n<\/pre>\n
Listing 1-3: The ChangeCodeDescription<\/span> stored procedure<\/p>\n
Listing 1-4 runs a simple smoke test on the new procedure.<\/p>\n
INSERT  INTO dbo.Codes\n        ( Code, Description )\nVALUES  ( 'IL', 'Ill.' ) ; \nGO \n\nEXEC dbo.ChangeCodeDescription\n  @Code = 'IL', \n  @Description = 'Illinois' ; \nGO \n\nSELECT  Code ,\n        OldDescription + ', ' + NewDescription\nFROM    dbo.CodeDescriptionsChangeLog ;  \n\nCode       \n---------- ---------------------------------------------------------\nIL         Ill., Illinois\n<\/pre>\n
Listing 1-4: A smoke test on the ChangeCodeDescription<\/span> stored procedure<\/p>\n
It looks like the procedure works, right? Note, however, that this stored procedure does not attempt to determine whether or not either of the two modifications failed, and it does not handle possible errors. Of course, one might argue that this stored procedure, could be a component of a perfectly valid system, if it is invoked by an application that does all the error handling. However, that does not make it, as is, a valid component. There’s a huge risk that a developer who builds another application may find this procedure and decide to call it, unaware of the required error handling in the calling procedure.<\/p>\n
It may seem that nothing could possibly go wrong during these two trivial modifications, but we still cannot assume that both modifications will always succeed. In fact, even in this trivial example, we can devise a case where one modification can fail: if two modifications occur simultaneously, we may get a primary key violation on the CodeDescriptionsChangeLog<\/span> table.<\/p>\n
Rather than reproduce that case here, we can prove the same point simply by creating a CHECK<\/span> constraint that prohibits inserts and updates against the CodeDescriptionsChangeLog<\/span> table, and demonstrates what happens when one of our modifications fails and we do nothing to detect and handle it.<\/p>\n
SET XACT_ABORT OFF ;\n-- if  XACT_ABORT OFF were set to ON ,\n-- the code below would behave differently.\n-- We shall discuss it later in this chapter.\n\nDELETE   FROM dbo.CodeDescriptionsChangeLog ;\n\nBEGIN TRANSACTION ;\nGO\n\n-- This constraint temporarily prevents all inserts\n-- and updates against the log table.\n-- When the transaction is rolled back, the constraint\n-- will be gone.\nALTER TABLE dbo.CodeDescriptionsChangeLog \nADD CONSTRAINT CodeDescriptionsChangeLog_Immutable \n   CHECK(1<0) ;\nGO\n\nEXEC dbo.ChangeCodeDescription\n  @Code = 'IL',\n  @Description = 'other value' ; \nGO\n\n-- dbo.Codes table has been updated\nSELECT   Code ,\n         Description\nFROM     dbo.Codes ; \n\n-- dbo.CodeDescriptionsChangeLog has not been updated\nSELECT   Code ,\n         OldDescription + ', ' + NewDescription\nFROM     dbo.CodeDescriptionsChangeLog ; \nGO\n\nROLLBACK ;\n\nMsg 547, Level 16, State 0, Procedure ChangeCodeDescription, Line 6\nThe INSERT statement conflicted with the CHECK constraint \"CodeDescriptionsChangeLog_Immutable\". The conflict occurred in database \"Test\", table \"dbo.CodeDescriptionsChangeLog\".\nThe statement has been terminated.\n\n(1 row(s) affected)\nCode       Description\n---------- ----------------------------------------\nIL         other value\n\n(1 row(s) affected)\n\nCode       \n---------- ----------------------------------------------------------\n\n(0 row(s) affected)\n<\/pre>\n
Listing 1-5: An INSERT<\/span> into CodeDescriptionsChangeLog<\/span> fails, but the UPDATE<\/span> of Codes<\/span> succeeds, and we end up with an UPDATE<\/span> that has not been logged<\/p>\n
In order to avoid this situation, we need to begin a transaction, attempt to do both modifications, determine whether or not both completed successfully, and commit the transaction only if both modifications succeeded. If either modification failed, we need to rollback the transaction, as part of our error handling. T-SQL allows several ways to accomplish that. Let’s begin with the simplest approach: using transactions along with the XACT_ABORT<\/span> setting.<\/p>\n
Using Transactions and XACT_ABORT to Handle Errors<\/h1>\n
In many cases, we do not need sophisticated error handling. Quite frequently all we need to do, in case of an error, is roll back all the changes and throw an exception, so that the client knows that there is a problem and will handle it. In such situations, a perfectly reasonable approach is to make use of the XACT_ABORT<\/span> setting.<\/p>\n
By default, in SQL Server this setting is OFF<\/span>, which means that in some circumstances SQL Server can continue processing when a T-SQL statement causes a run-time error. In other words, for less severe errors, it may be possible to rollback only the statement that caused the error, and to continue processing other statements in the transaction.<\/p>\n
If XACT_ABORT<\/span> is turned on, SQL Server stops processing as soon as a T-SQL run-time error occurs, and the entire transaction is rolled back. When handling unexpected, unanticipated errors, there is often little choice but to cease execution and rollback to a point where there system is in a ‘known state’. Otherwise you risk seeing partially completed transactions persisted to your database, and so compromising data integrity. In dealing with such cases, it makes sense to have XACT_ABORT<\/span> turned ON<\/span>.<\/p>\n
\n
Data Modifications via OLDE DB<\/b>
Note that in some cases XACT_ABORT<\/span> is already set to ON<\/span> by default. For example, OLE DB will do that for you. However, it is usually preferable to explicitly set it, because we do not know in which context our code will be used later.<\/p>\n<\/div>\n
Listing 1-6 illustrates a basic error handling approach, whereby our modifications take place within an explicit transaction, having set XACT_ABORT<\/span> to ON<\/span>. The PRINT<\/span> commands in the procedure are for demonstration purposes only; we would not need them in production code. <\/p>\n
ALTER PROCEDURE dbo.ChangeCodeDescription\n    @Code VARCHAR(10) ,\n    @Description VARCHAR(40)\nAS \n    BEGIN ; \n        SET XACT_ABORT ON ;\n        BEGIN TRANSACTION ; \n        INSERT  INTO dbo.CodeDescriptionsChangeLog\n                ( Code ,\n                  ChangeDate ,\n                  OldDescription ,\n                  NewDescription\n                )\n                SELECT  Code ,\n                        current_timestamp ,\n                        Description ,\n                        @Description\n                FROM    dbo.Codes\n                WHERE   Code = @Code ; \n        PRINT 'First modifications succeeded' ;\n\n        UPDATE  dbo.Codes\n        SET     Description = @Description\n        WHERE   Code = @Code ; \n  -- the following commands execute only if both\n  -- modifications succeeded\n        PRINT 'Both modifications succeeded, committing\n               the transaction' ;\n        COMMIT ;\n    END ;\n<\/pre>\n
Listing 1-6: Using the XACT_ABORT<\/span> setting and an explicit transaction<\/p>\n
Note that although we want to roll back all the changes if an error occurs, we do not need to explicitly determine if there are any errors, and we do not need to explicitly invoke ROLLBACK<\/span> in our code; when XACT_ABORT<\/span> is set to ON<\/span>, it all happens automatically. Listing 1-7 tests our altered stored procedure.<\/p>\n
SET NOCOUNT ON ;\nSET XACT_ABORT OFF ;\n\nDELETE   FROM dbo.CodeDescriptionsChangeLog ;\n\nBEGIN TRANSACTION ;\nGO\n\n-- This constraint temporarily prevents all inserts\n-- and updates against the log table.\n-- When the transaction is rolled back, the constraint\n-- will be gone.\nALTER TABLE dbo.CodeDescriptionsChangeLog \nADD CONSTRAINT CodeDescriptionsChangeLog_Immutable \n   CHECK(1<0) ;\nGO\n\nEXEC dbo.ChangeCodeDescription\n  @Code = 'IL',\n  @Description = 'other value' ; \nGO\n-- transaction is rolled back automatically\nSELECT @@TRANCOUNT AS [@@TRANCOUNT after stored procedure call] ;\n\n-- dbo.Codes table has not been updated\nSELECT   Code ,\n         Description\nFROM     dbo.Codes ; \n\n-- dbo.CodeDescriptionsChangeLog has not been updated\nSELECT   Code ,\n         OldDescription + ', ' + NewDescription\nFROM     dbo.CodeDescriptionsChangeLog ; \n\nMsg 547, Level 16, State 0, Procedure ChangeCodeDescription, Line 8\nThe INSERT statement conflicted with the CHECK constraint \"CodeDescriptionsChangeLog_Immutable\". The conflict occurred in database \"test\", table \"dbo.CodeDescriptionsChangeLog\".\n@@TRANCOUNT after stored procedure call\n---------------------------------------\n0\n\nCode       Description\n---------- ----------------------------------------\nIL         Illinois\n\nCode       \n---------- -----------------------------------------\n<\/pre>\n
Listing 1-7: Testing the altered stored procedure<\/p>\n
As we have seen, the stored procedure worked perfectly well. Of course, this is just the first in as series of tests we should perform on our stored procedure. Complete testing would include:<\/p>\n