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 TRY/CATCH constructs to detect and handle errors. I consider this new school (modern) error handling since it was introduced with SQL Server 2005.
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.
I will be calculating the Fibonacci sequence using the sample program below. It uses many of the control of flow statements discussed previously. The program will have an arithmetic overflow error at iteration 94.
|
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 |
-- -- New school error handling -- -- 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) ); -- -- My program with TRY/CATCH construct -- -- Try these steps BEGIN TRY -- Declare variables DECLARE @FN1 BIGINT = 0; DECLARE @FN2 BIGINT = 1; DECLARE @FN3 BIGINT = 0; DECLARE @CNT INT = 1; -- Insert first two values INSERT INTO [TBL_FIBONACCI] VALUES (@FN1, 1, GETDATE()), (@FN2, 2, GETDATE()); -- Calculate the first 100 fibonacci numbers WHILE (@CNT <= 100) BEGIN -- Calculate next value SET @FN3 = @FN2 + @FN1; -- Insert Fn two values INSERT INTO [TBL_FIBONACCI] VALUES (@FN3, @CNT + 2, GETDATE()); -- Move to next number in sequence SET @FN1 = @FN2; SET @FN2 = @FN3; -- Increment the counter SET @CNT += 1; END END TRY -- Error Handler BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH |
If you do not have a MATH database, create it before running the sample program. The first 93 numbers are stored in the table in the MATH database. The result window output of this program is shown below.
|
1 2 3 4 5 6 7 |
message window: Error Error Error Error Error Error Number Severity State Procedure Line Message ------ -------- ----- --------- ----- ------------------------------------- 8115 16 2 NULL 41 Arithmetic overflow error converting expression to data type bigint. |
In short, just wrap any code you want to detect and handle errors with the TRY (BEGIN/END) pair of commands. This will cause the program to branch to a the CATCH (BEGIN/END) statement block when an error is raised. The rest is up to you on how you want to handle the error.
The below system functions can be called to obtain more information on the detected error.
ERROR_NUMBER() – the numerical error number.
ERROR_SEVERITY() – the severity of the error.
ERROR_STATE() – actual part of database engine that raised the error.
ERROR_PROCEDURE() – the name of procedure or trigger that caused the error.
ERROR_LINE() – the line number of offending statement.
ERROR_MESSAGE() – the detailed textual explanation.
A quick run-down of this talk is the TRY/CATCH construct is a-lot simpler and more powerful than its predecessor, the @@ERROR function. Consider using it in the future.
Next time I will be talking about how you can THROW or RAISE an user defined error.
