{"id":5930,"date":"2013-07-02T00:00:15","date_gmt":"2013-07-02T00:00:15","guid":{"rendered":"http:\/\/craftydba.com\/?p=5930"},"modified":"2016-04-22T12:24:15","modified_gmt":"2016-04-22T12:24:15","slug":"error-handling-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5930","title":{"rendered":"Error Handling – Part 2"},"content":{"rendered":"

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. <\/p>\n

Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.<\/p>\n

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.<\/p>\n

Today, I want to talk about how to use the TRY\/CATCH<\/a> constructs to detect and handle errors. I consider this new school (modern) error handling since it was introduced with SQL Server 2005.<\/p>\n

There are two schools of thought when it comes to handling errors. <\/p>\n

    \n
  1. \nExit 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.\n<\/li>\n

    <\/p>\n

  2. \nUpon 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.\n<\/li>\n<\/ol>\n

    I will be calculating the Fibonacci sequence<\/a> 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.<\/p>\n

    \r\n--\r\n-- New school error handling\r\n--\r\n\r\n-- Remove the existing table\r\nIF OBJECT_ID('[MATH].[dbo].[TBL_FIBONACCI]') IS NOT NULL\r\n    DROP TABLE [MATH].[dbo].[TBL_FIBONACCI];\r\n\r\n-- Create a new table\r\nCREATE TABLE [TBL_FIBONACCI]\r\n(\r\n    [MY_VALUE] [bigint] NOT NULL,\r\n    [MY_NUMBER] [bigint] NOT NULL,\r\n    [MY_TIME] [datetime] NOT NULL,\r\n    CONSTRAINT [PK_TBL_FIBONACCI] PRIMARY KEY CLUSTERED \r\n    ([MY_NUMBER] ASC)\r\n);\r\n\r\n-- \r\n-- My program with TRY\/CATCH construct\r\n--\r\n\r\n-- Try these steps\r\nBEGIN TRY\r\n\r\n    -- Declare variables\r\n    DECLARE @FN1 BIGINT = 0;\r\n    DECLARE @FN2 BIGINT = 1;\r\n    DECLARE @FN3 BIGINT = 0;\r\n    DECLARE @CNT INT = 1;\r\n\r\n    -- Insert first two values\r\n    INSERT INTO [TBL_FIBONACCI] VALUES \r\n    (@FN1, 1, GETDATE()),\r\n    (@FN2, 2, GETDATE());\r\n\r\n    -- Calculate the first 100 fibonacci numbers\r\n    WHILE (@CNT <= 100)\r\n    BEGIN\r\n        -- Calculate next value\r\n        SET @FN3 = @FN2 + @FN1;\r\n\r\n        -- Insert Fn two values\r\n        INSERT INTO [TBL_FIBONACCI] VALUES \r\n        (@FN3, @CNT + 2, GETDATE());\r\n\r\n        -- Move to next number in sequence\r\n        SET @FN1 = @FN2;\r\n        SET @FN2 = @FN3;\r\n\r\n        -- Increment the counter\r\n        SET @CNT += 1;\r\n    END\r\nEND TRY\r\n\r\n-- Error Handler\r\nBEGIN CATCH\r\n    SELECT\r\n        ERROR_NUMBER() AS ErrorNumber\r\n       ,ERROR_SEVERITY() AS ErrorSeverity\r\n       ,ERROR_STATE() AS ErrorState\r\n       ,ERROR_PROCEDURE() AS ErrorProcedure\r\n       ,ERROR_LINE() AS ErrorLine\r\n       ,ERROR_MESSAGE() AS ErrorMessage;\r\nEND CATCH\r\n\r\n<\/pre>\n<\/p>\n
    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.  <\/p>\n
    \r\nmessage window: \r\n\r\nError    Error      Error  Error      Error   Error\r\nNumber   Severity   State  Procedure  Line    Message\r\n------   --------   -----  ---------  -----   -------------------------------------\r\n8115     16         2      NULL       41      Arithmetic overflow error converting \r\n                                              expression to data type bigint.\r\n\r\n<\/pre>\n<\/p>\n
    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.<\/p>\n
    The below system functions can be called to obtain more information on the detected error. <\/p>\n
    ERROR_NUMBER()<\/a> - the numerical error number.
    \n    ERROR_SEVERITY()<\/a> - the severity of the error.
    \n    ERROR_STATE()<\/a> - actual part of database engine that raised the error.
    \n    ERROR_PROCEDURE()<\/a> - the name of procedure or trigger that caused the error.
    \n    ERROR_LINE()<\/a> - the line number of offending statement.
    \n    ERROR_MESSAGE()<\/a> - the detailed textual explanation.<\/p>\n
    A quick run-down of this talk is the TRY\/CATCH<\/a> construct is a-lot simpler and more powerful than its predecessor, the @@ERROR<\/a> function.  Consider using it in the future.<\/p>\n
    Next time I will be talking about how you can THROW<\/a> or RAISE<\/a> an user defined error.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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…<\/p>\n","protected":false},"author":1,"featured_media":5878,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[322,667,31,871,671,882,881,15,28,666,29],"class_list":["post-5930","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-dev","tag-begin","tag-catch","tag-database-developer","tag-end","tag-error-handling","tag-error-system-functions","tag-fibonacci-sequence","tag-john-f-miner-iii","tag-sql-server","tag-try","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5930","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5930"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5930\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/5878"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5930"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5930"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5930"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}