Tutorials
Practical T-SQL
Practical T-SQL
  • Practical T-SQL Pocket Guide For Beginners
  • Preface
    • Section I. A Note From The Author
    • Section II. Tutorial Overview
    • Section III. Running The Examples
    • Section IV. How To Report An Issue
    • Section V. Join The MSU Community On Discord
    • Section VI. Supplementary Material
  • Language Basics
    • Lesson 1. Obligatory Hello World
    • Lesson 2. Code Comments With T-SQL
    • Lesson 3. Basic Syntax
    • Lesson 4. Your First Query
    • Lesson 5. Filtering Data
    • Lesson 6. Sorting Data
    • Lesson 7. Complex Data Filtering
    • Lesson 8. Aliases
    • Lesson 9. String Functions
    • Lesson 10. Creating New Columns From Existing Data (Calculated Fields)
    • Lesson 11. Displaying Data Based On Conditions (Case Statement)
    • Lesson 12. Aggregate Functions
    • Lesson 13. Grouping And Summarizing Data
    • Lesson 14. Querying More Than One Table
    • Lesson 15. Combining Queries
    • Lesson 16. Subqueries
    • Lesson 17. Creating Data
    • Lesson 18. Updating Data
    • Lesson 19. Deleting Data
    • Lesson 20. Common Table Expressions (CTEs)
    • Lesson 21. Derived Tables
    • Lesson 22. Putting It All Together
  • Advanced Topics
    • Lesson 23. Selecting Unique Values
    • Lesson 24. Updating Data With A Join
    • Lesson 25. Data Types
    • Lesson 26. Casting Data Types
    • Lesson 27. Creating Tables
    • Lesson 28. Altering Tables
    • Lesson 29. Dropping Tables
    • Lesson 30. Variables
    • Lesson 31. Controlling Flow
    • Lesson 32. Looping
    • Lesson 33. Error Processing
    • Lesson 34. Temporary Tables
    • Lesson 35. Views
    • Lesson 36. Indexed Views
    • Lesson 37. User Defined Functions
    • Lesson 38. Stored Procedures
    • Lesson 39. BULK INSERT
    • Lesson 40. Loading Tables With MERGE
    • Lesson 41. Partitioning A Dataset
    • Lesson 42. Pivoting Data
    • Lesson 43. Dynamic SQL
    • Lesson 44. Cursors
  • Solutions To Real World Problems
    • Lesson 45. Listing All Tables In A SQL Server Database
    • Lesson 46. Listing All Columns In A SQL Server Database
    • Lesson 47. Pull Records From A Table At Random
    • Lesson 48. A Better Alternative To WITH (NOLOCK)
    • Lesson 49. Boost Performance When Calling A Stored Proc From SSIS
    • Lesson 50. Setting Up Queries For Ablation Testing
    • Lesson 51. Reduce Code And Save Time With Default Column Values
    • Lesson 52. Finding Duplicate Records In A Table
    • Lesson 53. Why You Cannot Have More Than One Clustered Index On A Table
    • Lesson 54. Converting Dates To YYYYMMDD
    • Lesson 55. Sending Notification Emails With T-SQL Without Using Hardcoded Email Addresses
    • Lesson 56. Troubleshooting Long Running Queries
    • Lesson 57. Loading Large CSVs Into Data Warehouse Staging Tables
    • Lesson 58. The Only Bloody Good Reason To Use Cursors
    • Lesson 59. Loading A Type II Slowly Changing Dimension With SQL Merge
    • Lesson 60. A Clearer Explanation Of The Parameters Of The Numeric Data Type
    • Lesson 61. Why You Cannot Join On Null Values
    • Lesson 62. A Deep Dive On How The Where Clause Functions
    • Lesson 63. Using HASHBYTES() To Compare Character Strings
    • Lesson 64. Using Pipe To Hash Multiple Columns For Matching
    • Lesson 65. Why People That Indent Code Drive Me Nuts
    • Lesson 66. How To Rapidly Stand Up A Data Warehouse From Scratch
    • Lesson 67. How To Pivot Data With T-SQL When Columns Are Not Predefined
    • Lesson 68. Prepopulating A Junk Dimension
Powered by GitBook
On this page
  1. Advanced Topics

Lesson 33. Error Processing

Error processing is important in any programming language. Weird things will happen, and your code has to be ready to handle weirdness. Sometimes you know what weirdness to expect like a variable value being NULL, and you have to program what to do in that instance. Other times, the user will do something unexpected and you have to write a general error handler.

In any case, the point of error processing is to keep the program going when an error happens. The general process is:

  1. Trap the error.

  2. Perform alternative task.

  3. Report the error.

T-SQL has a way to trap errors with the standard TRY CATCH approach that you will find in most major programming languages. The general syntax of a TRY CATCH block is:

BEGIN TRY --Process that may create an error END TRY BEGIN CATCH --Process to handle error END CATCH

T-SQL has a handful of functions that help with error reporting.

Function Name
Function Definition

ERROR_LINE

The line number the error occurred on.

ERROR_MESSAGE

Plain language description of the error.

ERROR_NUMBER

The number of the error.

ERROR_PROCEDURE

The name of the function or stored procedure that produced the error.

ERROR_SEVERITY

The severity value of the error.

ERROR_STATE

The state number of the error.

The error number, state, and severity are particularly useful. There is a long list of database engine errors that correspond to a number. You can check for that value in an equality when building your error handling logic.

It is rare when I do error processing. When I build data warehouse load processes, each stored procedure I write performs a single task. An example would be a stored procedure that does nothing but load a single dimension table. The entire load process is engineered so that an error brings the entire thing to a halt. I do this to prevent train wrecks. Load processing is a series of events that have to happen in order. When a load processes in any other way than intended, then the train wreck metaphor becomes readily apparent.

Since my processes run in SQL Server Agent, any errors are logged without me having to write code. However, from time to time, I do have to write errors. Recently, I had to write code that was going to be leveraged by an application written in .NET. In this case, reporting errors was necessary so they could be reported to the user.

Examples

Trapping Divide By Zero

In [ ]:

USE demo

BEGIN TRY
Print 1/0
END TRY
BEGIN CATCH
PRINT 'Error '+CAST(ERROR_NUMBER()AS NVARCHAR(6))+' '+ERROR_MESSAGE()
END CATCH

Creating A Custom Error Message

In [ ]:

USE demo

DECLARE @UserInput INT

BEGIN TRY
SET @UserInput = 'Wakefield'
END TRY
BEGIN CATCH
PRINT 'Please input an integer value.'
END CATCH
PreviousLesson 32. LoopingNextLesson 34. Temporary Tables

Last updated 3 years ago