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:

Start Microsoft SQL Server

In the Server Name combo box, select the
name of the server or type it

In the Authentication combo box, select SQL Server Authentication

In the User Name combo box, type the name of the domain, followed
by \, and followed by the login name you were given

In the Password combo box, type your password

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:

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:

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 TRYNormal codeEND TRY

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

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:

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:

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.

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:

You can then get the error number to take action. Here
is an example of finding out the 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:

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;

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.

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:

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.

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:

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.

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:

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:

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