Home

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 LearningPractical Learning: Introducing Exception Handling

  1. All Computers: Start the computer and log in using your domain account
  2. 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
  3. 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
  4. All Computers: On the Standard toolbar, click the New Query button New Query
  5. Server: To create a new database, type the following
    CREATE DATABASE Exercise2;
    GO
    
    USE Exercise1;
    GO
  6. Server: Press F5 to execute
  7. Each Student: Type the following:
    USE Exercise2;
    GO
  8. 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
     

    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:
     

    Overflow


    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:

Handling an Exception
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 LearningPractical Learning: Creating an Exception

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

  1. 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
  2. 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 LearningPractical Learning: Getting an Error Line Number

  1. 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
  2. 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();

 

 
 
 
 
Exceptions Fundamentals   

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 LearningPractical Learning: Checking an Error Number

  1. 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
  2. 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 LearningPractical Learning: Getting an Error Message

  1. 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
  2. Press F5 to execute.
    This would produce:

    Error Message

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 LearningPractical Learning: Getting the Severity of an Error

  1. 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
  2. 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 LearningPractical Learning: Exploring Exceptions

  1. 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
  2. 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 LearningPractical Learning: Nesting Exceptions

  1. 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
  2. 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 LearningPractical Learning: Ending the Lesson

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

Previous Copyright © 2003-2011 FunctionX.com Next