Microsoft SQL Server Classroom - Lesson 11: Exception Handling

	Exception Handling

Exceptions Fundamentals

Introduction

Some problems will happen during the lifetime of a database application. You cannot eliminate this fact. Instead, you can anticipate as many problems as possible and take appropriate actions. Normally, Microsoft SQL Server provides some means of taking care of problems.

In reality, when it comes to a Microsoft SQL Server database application, you can take care of problems on either the Microsoft SQL Server side or on a programming environment you are using to create a graphical application.

Practical Learning: Introducing Exception Handling

All Computers: Start the computer and log in using your domain account
Server: Launch Microsoft SQL Server. In the Server Name combo box, make sure the name of the computer is selected. In the Authentication combo box, make sure Windows Authentication is selected. Make sure the account you are using is selected in the User Name combo box. Click Connect
Each Student:
1. Start Microsoft SQL Server
2. In the Server Name combo box, select the name of the server or type it
3. In the Authentication combo box, select SQL Server Authentication
4. In the User Name combo box, type the name of the domain, followed by \, and followed by the login name you were given
5. In the Password combo box, type your password
6. Click Connect
All Computers: On the Standard toolbar, click the New Query button
Server: To create a new database, type the following
```
CREATE DATABASE Exercise2;
GO

USE Exercise1;
GO
```
Server: Press F5 to execute
Each Student: Type the following:
```
USE Exercise2;
GO
```
Each Student: Press F5 to execute

Types of Errors: Syntax Errors

There are various categories of errors you can deal with. A syntax error occurs if you try writing code that Transact-SQL does not allow. Examples are:

If you try typing an operator or a keyword where it should not be, the Code Editor would show it to you. Here is an example:

Syntax Error

This error is because the SET operator, although part of the Transact-SQL, was used wrongly.

If you wrongly type a keyword or an operator probably because you don't remember it, the Code Editor would signal it. Here is an example:

Syntax Error

If you forget to type something necessary or required, when you try executing the code, it would produce an error

Syntax errors are usually easy to detect because the Code Editor points them out right away. Consequently, these errors are easy to fix.

If you use a command-based application such as SQLCMD or PowerShell, it would not show the error right way. It would show it when you execute the code.

Types of Errors: Run-Time Errors

A run-time error is the type that occurs if your application tries to perform an operation that either or both Microsoft SQL Server and/or the operating system do not allow. These errors can be difficult to fix because sometimes they are not clear, or what happens as the error is not clearly identified or is external to the database. The problem could be that, when testing the database in Microsoft SQL Server, it may work just fine, but after the application has been distributed and is used, problems start occurring.

Examples of run-time errors are:

Trying to execute code that is not available or is not clearly defined
Performing a bad calculation such as a division by 0

Notice that the Code Editor does not signal any problem, because this is not a syntax error
Trying to use a function, a stored procedure, or a trigger that is not available
Using or accessing computer memory that is not available or enough
Trying to perform an operation that either a variable or an object cannot handle. An example is trying to store in a variable a value that is beyond its allowable range. Here is an example:

Notice that the Code Editor does not signal any problem, because this is not a syntax error

Performing an operation on incompatible types
Wrongly using a conditional statement, or using a mis-constructed conditional statement

Run-time errors can be difficult to locate and fix.

Handling an Exception

Trying an Exception

Exception handling is the ability to deal with errors that occur or can occur on a database. The error is called an exception. To assist you with handling exceptions, Transact-SQL provides a general formula. You start with a section as follows:

BEGIN TRY
    Normal code
END TRY

Between the BEGIN TRY and the END TRY lines, write the normal code you want to execute. Here is an example.

BEGIN TRY
    DECLARE @Side decimal(6, 3),
	    @Perimeter decimal(6, 3);
	
    SET @Side = 124.36;
    SET @Perimeter = @Side * 4;
	
    SELECT @Side AS Side, @Perimeter AS Perimeter;
END TRY

The section of code that starts from BEGIN TRY to END TRY is called a try block or a try clause.

Catching an Exception

After the try block, you must create another section that starts with BEGIN CATCH and ends with END CATCH:

BEGIN TRY
    Normal code
END TRY
BEGIN CATCH
    
END CATCH

The section of code that goes from BEGIN CATCH to END CATCH is called a catch block or a catch clause.

Among the rules you must observe:

If you create a try block, you must also create a catch block
There must not be any Transact-SQL code (except a comment, that is not SQL code anyway) between the END TRY and the BEGIN CATCH lines

If no error happens in the try block, you can leave the catch block empty. Here is an example:

USE Exercise;
Go

BEGIN TRY
    DECLARE @Side decimal(6, 3),
	    @Perimeter decimal(6, 3);
	
    SET @Side = 124.36;
    SET @Perimeter = @Side * 4;
	
    SELECT @Side AS Side, @Perimeter AS Perimeter;
END TRY
BEGIN CATCH

END CATCH

This would produce:

Normal

Imagine you write code that could produce an error:

USE Exercise;
GO

DECLARE @Number tinyint,
       @Result tinyint;
	
SET @Number = 252;
SET @Result = @Number + 20;
	
SELECT @Number AS Number, @Result AS Result;
GO

This would produce:

Error

To address this type of problem, you can use exception handling and include the normal code in a try block. Then, if an error occurs in the try block, you can use the catch block to display a message. Here is an example:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'There was a problem with the program';
END CATCH

On the other hand, if no error occurs in the try block, that try block executes but when it ends, the execution skips the catch block and continues execution with code below the END CATCH line, if any.

Practical Learning: Creating an Exception

From here to the end of this lesson, all instructions are intended for All Computers:

Select the whole text in the editor and type the following:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'There was a problem with the program';
END CATCH
GO

Press F5 to execute.
This would produce:

Error

Identifying an Error

Introduction

To assist you with identifying an error that has occurred, Transact-SQL provides various functions.

The Error Line

When an error occurs in your code, probably the first thing you want to know is where the error occurred in your code. To assist you with this, Transact-SQL provides a function named ERROR_LINE. Its syntax is:

int ERROR_LINE();

This function doesn't take an argument. It returns a number that represents the line number where the error occurred.

Using a conditional statement, you can question the database engine to know the line where the error occurred. With this information, you can take the necessary action.

Practical Learning: Getting an Error Line Number

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
            @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'Error Line: ' + CAST(ERROR_LINE() AS nvarchar(100));
END CATCH
GO

Press F5 to execute.
This would produce:

The Error Number

Every type of error is recognized with a specific number, which is just a type of identity (we will see how you can use that number; but, as a numeric value, that number doesn't indicate anything). To know the number of an error, you can call the ERROR_NUMBER() function. Its syntax is:

int ERROR_NUMBER();

This function takes no argument and returns an integer. Here is an example of calling it:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    ERROR_NUMBER();
END CATCH

You can then get the error number to take action. Here is an example of finding out the number:

Error Number

To find out what error number was produced by your code, you can inquire about the value produced by the ERROR_NUMBER() function. To do that, you can write an IF conditional statement.

If/once you know the error number, you can take an appropriate action. At the least you can display a message. Here is an example:

Error Number

Of course, you can take better action than that.

In previous versions of Microsoft SQL Server, the means of getting an error number was to call a function named @@ERROR. You can still use this function to find out what the error number is in order to take an appropriate action. Its syntax is:

int @@ERROR();

This function can be called to get the error number produced by an exception. Here is an example;

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'Error #: ' + CAST(@@ERROR AS NVARCHAR(50));
END CATCH

This would produce:

Error

Just as done for the ERROR_NUMBER() function, you can check the value of the @@ERROR call to find out what the error number is, and if it is the right number you are looking for, you can take appropriate action.

Practical Learning: Checking an Error Number

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    IF @@ERROR = 220
        PRINT N'Something went wrong with your code';
END CATCH
GO

Press F5 to execute

The Error Message

An error number is just a number built in the function code and known by the database engine. That number does not give any meaningful indication about the error. To give you a message related to the error, Transact-SQL provides the ERROR_MESSAGE() function. Its syntax is:

nvarchar ERROR_MESSAGE();

This function takes no argument and it returns a string. Here is an example of calling it:

USE Exercise;
GO

BEGIN TRY
	DECLARE @Number tinyint,
	        @Result tinyint;
	
	SET @Number = 252;
	SET @Result = @Number + 20;
	
	SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE();
END CATCH

This would produce:

Error Message

Because you are a programmer, you should understand the meaning of the words overflow, error, and tinyint. Unfortunately, this message may not be very clear to a regular user. For this reason, you should provide an easy way to read the message. You can even combine your own error message to the value of the ERROR_MESSAGE() function.

Practical Learning: Getting an Error Message

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'Error Message: ' + ERROR_MESSAGE();
END CATCH
GO

Press F5 to execute.
This would produce:

The Severity of an Error

Errors have different levels of consideration. Some must be dealt with as soon as possible while others can wait. To help you identify the severity of an error, Transact-SQL provides the ERROR_SEVERITY() function. Its syntax is:

int ERROR_SEVERITY();

This function takes no argument and returns an integer. Here is an example of calling it to identify the severity of an error:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;

    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'Error Severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(100));
END CATCH

This would produce:

Error Severity

The value of this number is not a level of severity. It is just an indication of the severity. You as the database developer must find out what this number is and take appropriate action. You can write an IF conditional statement to find out the value produced by this function and do what you judge necessary.

Practical Learning: Getting the Severity of an Error

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 20;
	
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    IF ERROR_SEVERITY() = 16
	PRINT N'An error has occurred on the database.';
	PRINT N'---------------------------------------------';
	PRINT N'The error severity number is 16.';
	PRINT N'Don''t panic at this time. Contact the';
	PRINT N'database administrator and specify this number.';
END CATCH
GO

Press F5 to execute.
This would produce:

The State of an Error

The state of an error is a number that specifies the section of code where an error occurred. This is because the same code can produce different errors at different sections of the code. To help you identify the state of an error, Transact-SQL provides the ERROR_STATE() function. Its syntax is:

int ERROR_STATE();

This function takes no argument. It returns an integer that specifies the state of the error. This function is used with the same approach as the severity of an error.

Other Characteristics of Exception Handling

Introduction

Just as you can write various statements in your code, you can also create various exception sections. Here are examples:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	    @Result tinyint;
	
    SET @Number = 252;
    SET @Result = @Number + 2;
	
    PRINT N'Number = ' + CAST(@Number AS nvarchar(20));
    PRINT N'Result = ' + CAST(@Result AS nvarchar(20));
END TRY
BEGIN CATCH
    PRINT N'Error: ' + ERROR_MESSAGE();
END CATCH

PRINT N'---------------------------------------';

BEGIN TRY
    DECLARE @Value decimal(6,2),
	    @Division decimal;
	
    SET @Value = 15.50;
    SET @Division = @Value / 2;
	
    PRINT N'Number = ' + CAST(@Value AS nvarchar(20));
    PRINT N'Result = ' + CAST(@Division AS nvarchar(20));
END TRY
BEGIN CATCH
    PRINT N'Error: ' + ERROR_MESSAGE();
END CATCH

This would produce:

Excception Handling

In this case, each section would handle its own exception.

Practical Learning: Exploring Exceptions

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    DECLARE @Number tinyint,
	        @Result tinyint;
	
	SET @Number = 252;
	SET @Result = @Number + 42;
	
	PRINT N'Number = ' + CAST(@Number AS nvarchar(20));
	PRINT N'Result = ' + CAST(@Result AS nvarchar(20));
END TRY
BEGIN CATCH
	PRINT N'Error: ' + ERROR_MESSAGE();
END CATCH

PRINT N'---------------------------------------';

BEGIN TRY
	DECLARE @Value decimal(6,2),
			@Division decimal;
	
	SET @Value = 15.50;
	SET @Division = @Value / 0;
	
    PRINT N'Number = ' + CAST(@Value AS nvarchar(20));
	PRINT N'Result = ' + CAST(@Division AS nvarchar(20));
END TRY
BEGIN CATCH
	PRINT N'Error: ' + ERROR_MESSAGE();
END CATCH
GO

Press F5 to execute.
This would produce:

Multiple Exceptions

Nesting an Exception

You can create an exception handling code inside of another. This is referred to as nesting. The basic formula is:

BEGIN TRY
    BEGIN TRY
	-- Nested try block
    END TRY
    BEGIN CATCH
	-- Nested catch block
    END CATCH
END TRY
BEGIN CATCH

END CATCH

Practical Learning: Nesting Exceptions

Change the code in the text editor as follows:

USE Exercise;
GO

BEGIN TRY
    BEGIN TRY
        DECLARE @Number tinyint,
	            @Result tinyint;
	
    	SET @Number = 252;
	    SET @Result = @Number + 42;
	
    	PRINT N'Number = ' + CAST(@Number AS nvarchar(20));
	    PRINT N'Result = ' + CAST(@Result AS nvarchar(20));
    END TRY
    BEGIN CATCH
		PRINT N'Error: ' + ERROR_MESSAGE();
    END CATCH

    PRINT N'---------------------------------------';

    BEGIN TRY
	    DECLARE @Value decimal(6,2),
		    	@Division decimal;
	
    	SET @Value = 15.50;
	    SET @Division = @Value / 0;
	
	    PRINT N'Number = ' + CAST(@Value AS nvarchar(20));
		PRINT N'Result = ' + CAST(@Division AS nvarchar(20));
    END TRY
    BEGIN CATCH
		PRINT N'Error: ' + ERROR_MESSAGE();
    END CATCH
END TRY
BEGIN CATCH
    PRINT N'There was a problem with your code';
END CATCH
GO

Press F5 to execute

Raising an Error

If an error occurs in your code, you can take initiative for it, as we have done so far. To better customize how an exception is handled when it occurs, you can raise an error. To support this, Transact-SQL provides the RAISERROR() function. Its syntax is:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

This function takes three required arguments:

The first argument can be one of three things:
- The argument can be represented as a constant integer. To start, create a message, assign it a number higher than 50000, and pass that message to Transact-SQL by storing it in the sys.messages library. If you do this, to access the message, you would use the number you specified
- The argument can be represented as a msg_str object. In this case, the argument is the message you want to produce (or display) if an error occurs. The argument is created and formatted like the printf() function of the C language
- The first argument can be a string-based locally declared variable. It is then initialized and formatted as done for the msg_str option
The second argument is a number that represents the severity level of the error. You specify this number as you see fit, knowing that you will manage it later as you see fit. The number specified for this argument should be between 0 and 18. If you are a member of the sysadmin group, you can specify a number higher than that. If you use a number between 20 and 25, this is considered very high (or a dangerous error) and can close the connection to the database
The third argument is a number that represents the error state. For this argument, you can specify any number between 1 and 127. If you are creating different exceptions sections, you should provide a unique state number for each

Practical Learning: Ending the Lesson

All Computers: Close the Query window
All Computers: When asked whether you want to save the file, click No
Server: Close Microsoft SQL Server
Server: Launch Microsoft SQL Server and click Connect
Server: In the Object Explorer, expand Databases
Server: Under Databases, right-click Exercise2 and click Delete
Server: In the dialog box, click OK
Server: Close Microsoft SQL Server