I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.
The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.
Today, I want to talk about how to use the @@ERROR system function to determine if an error occurred. This function returns the last returned error code. It is important to save this value right away in a variable since the next successfully executed statement overwrites the previous value.
I consider this old school error handling since many older programs, including SQL Server Management Studio job scripting, combine the check with a GOTO statement to one central error handler.
There are two schools of thought when it comes to handling errors.
- Exit the program upon detecting the error. Make sure the appropriate error code and message are logged. An operator will have to manually correct the problem and re-run the program.
- Upon detecting the error, wait some period of time (ms). Then, retry the operation. Hopefully the pause in program execution has allowed the system issue, such as a lock, to clear. After a number of tries, handle the error like step 1 above.
Let’s come up with an example program that will have a run-time error. During my studies in college, I took many math courses. One number theory course went over the Fibonacci numbers.
This sequence can be defined as the following recurrence relationship.
F(0) = 0
F(1) = 1
F(N) = F(N-1) + F(N-2)
The first 13 Fibonacci numbers are listed below.
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144 …
The cartoon below shows how eating nachos using the Fibonacci sequence as a chip selection algorithm will result in a stomach ache!
The best part of this sequence is that it has exponential growth. As a result, we will have an arithmetic overflow when calculating the 94th fibonacci number. I am using a BIGINT as variables in the calculation. Using a integer variable of lesser precision will make the overflow happen quicker.
The Fibonacci sample program is below. It uses many of the control of flow statements discussed previously.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
-- -- Old school error handling -- -- Use the correct database USE MATH GO -- Remove the existing table IF OBJECT_ID('[MATH].[dbo].[TBL_FIBONACCI]') IS NOT NULL DROP TABLE [MATH].[dbo].[TBL_FIBONACCI]; -- Create a new table CREATE TABLE [TBL_FIBONACCI] ( [MY_VALUE] [bigint] NOT NULL, [MY_NUMBER] [bigint] NOT NULL, [MY_TIME] [datetime] NOT NULL, CONSTRAINT [PK_TBL_FIBONACCI] PRIMARY KEY CLUSTERED ([MY_NUMBER] ASC) ); -- Ignore arithmetic warnings /* SET ARITHABORT OFF; SET ARITHIGNORE ON; SET ANSI_WARNINGS OFF; */ -- Allow arithmetic warnings SET ARITHABORT ON; SET ARITHIGNORE OFF; SET ANSI_WARNINGS ON; -- -- My program with IF @@ERROR construct -- -- Declare variables DECLARE @FN1 BIGINT = 0; DECLARE @FN2 BIGINT = 1; DECLARE @FN3 BIGINT = 0; DECLARE @ERR INT = 0; DECLARE @CNT INT = 1; -- Insert first two values INSERT INTO [TBL_FIBONACCI] VALUES (@FN1, 1, GETDATE()), (@FN2, 2, GETDATE()); -- Simple error handling SET @ERR = @@ERROR; IF (@ERR <> 0) PRINT 'CALCULATION: DID NOT EXPECT THAT FOR THE FIRST TWO FIBS!'; -- Calculate the first 100 fibonacci numbers WHILE (@CNT <= 100 AND @ERR = 0) BEGIN -- Calculate next value SET @FN3 = @FN2 + @FN1; -- Simple error handling SET @ERR = @@ERROR; IF (@ERR <> 0) BEGIN PRINT 'CALCULATION: DID NOT EXPECT THAT FOR FIB(' + LTRIM(STR(@CNT+2)) + ')'; BREAK; END; -- Insert Fn two values INSERT INTO [TBL_FIBONACCI] VALUES (@FN3, @CNT + 2, GETDATE()); -- Simple error handling SET @ERR = @@ERROR; IF (@ERR <> 0) BEGIN PRINT 'INSERT RECORD: DID NOT EXPECT THAT FOR FIB(' + LTRIM(STR(@CNT+2)) + ')'; BREAK; END; -- Move to next number in sequence SET @FN1 = @FN2; SET @FN2 = @FN3; -- Increment the counter SET @CNT += 1; END; GO |
The first 93 numbers are stored in the table in the MATH database. If you do not have a MATH database, create it before running the program.
The error output of this program is shown below.
|
1 2 3 4 5 6 7 |
message window: Msg 8115, Level 16, State 2, Line 57 Arithmetic overflow error converting expression to data type bigint. CALCULATION: DID NOT EXPECT THAT FOR FIB(94) </span> |
The first key point to take away from this talk is that the @@ERROR status needs to be saved and examined after any key action. If there is an error, you need to figure out how you want to handle it. The following white paper for SQL Server 2000 still has value for this technique.
The second key point to note is that certain server settings effect how errors are handled.
SET ARITHABORT – this should be set ON for optimal query plans. When set OFF, it will evaluate divide-by-zero and arithmetic overflow errors as NULL.
SET ANSI_WARNINGS – when this is set ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated.
SET ARITHIGNORE – this setting determines if error messages are returned.
In a nutshell, you should determine how you want to handle errors. Either abort the current transaction or retry a number of times. The @@ERROR function is still in heavy use in the world. You might even stumble upon central error handling via the GOTO statement. I consider this the old school way of handling errors.
Next time, I will be talking about the TRY/CATCH construct. This new school way of handling errors was introduced in SQL Server 2005.
