76
C
HAPTER
6
Error handling in SQL Server and applications
The
ERROR_STATE()
function can be used to determine the error state. Some sys-
tem error messages can be raised at different points in the
SQL
Server engine.
SQL
Server uses the error state to differentiate when these errors are raised.
The last two properties of an error are the line number and the name of the stored
procedure where the error occurred. These can be returned using the
ERROR_LINE()
function and the
ERROR_PROCEDURE()
function, respectively. The
ERROR_PROCEDURE()
function will return
NULL
if the error occurs outside a stored procedure. Listing 4 is an
example of these last two functions inside a stored procedure.
CREATE PROCEDURE ChildError
AS
BEGIN
RAISERROR('My Error', 11, 1)
END
GO
CREATE PROCEDURE ParentError

The first parameter is the custom error message. The second is the severity (or level).
Remember that 11 is the minimum severity that will cause a
CATCH
block to fire. The
last parameter is the error state.
Listing 4
ERROR_LINE
and
ERROR_PROCEDURE
functions in a stored procedure
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
77
Handling errors inside SQL Server

RAISERROR
can also be used to return user-created error messages. The code in list-
ing 5 illustrates this.
EXEC sp_addmessage
@msgnum = 50001,
@severity = 11,
@msgtext = 'My custom error',
@replace = 'replace';
GO
RAISERROR(50001, 11, 1);
GO
This returns the following result:
Msg 50001, Level 11, State 1, Line 1
My custom error
The

or
CATCH
blocks. Nesting inside a
TRY
block looks like listing 6.
BEGIN TRY
PRINT 'One'
BEGIN TRY
PRINT 1/0
END TRY
BEGIN CATCH
PRINT 'Caught by the inner catch'
END CATCH
PRINT 'Two'
END TRY
BEGIN CATCH
PRINT 'Caught by the outer catch'
END CATCH
Listing 5 Returning user-created error messages with
RAISERROR
Listing 6 Nesting
TRY...CATCH
blocks
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
78
C
HAPTER
6
Error handling in SQL Server and applications

@ErrorState,
@ErrorNumber )
END
END CATCH
PRINT 'Two'
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE()
END CATCH
This returns the following result:
One
Error: Conversion failed when converting datetime from character string.
(241)
In the inner
CATCH
block I’m checking whether we generated error number 8134
(divide by zero) and if so, I print a message. For every other error message, I “reraise”
or “rethrow” the error to the outer catch block. Note the text string that’s added to
Listing 7 Error handling with nested
TRY...CATCH
statements
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
79
Handling errors inside SQL Server
the error message variable. The
%d
is a placeholder that’s replaced by the first addi-
tional parameter passed to
RAISERROR

A common use for a
TRY...CATCH
block is to handle transaction processing. A com-
mon pattern for this is shown in listing 9.
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.invoice_header
(invoice_number, client_number)
VALUES (2367, 19)
INSERT INTO dbo.invoice_detail
(invoice_number, line_number, part_number)
VALUES (2367, 1, 84367)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
Listing 8 An error-handling module
Listing 9 Transaction processing in a
TRY...CATCH
block
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
80
C
HAPTER
6
Error handling in SQL Server and applications
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
-- And rethrow the error
END CATCH
Remember that the

SQL
Server–specific properties. A simple
example of this in C# is shown in listing 10.
using System.Data;
using System.Data.SqlClient;
class Program
{
SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand("RAISERROR('My Error', 11, 1)", conn);
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("Error Message: " + sqlex.Message);
Console.WriteLine("Error Severity: {0}", sqlex.Class.ToString());
Console.WriteLine("Line Number: {0}", sqlex.LineNumber.ToString());
}
}
This returns the following result:
Error Message: My Error
Error Severity: 11
Line Number: 1
Exceptions with a severity of 10 or less don’t trigger the
catch
block on the client. The
connection is closed if the severity level is 20 or higher; it normally remains open if

Errors
property. This is
a collection of
SqlError
objects. The
SqlError
class includes only the
SQL
Server–
specific properties from the
SqlException
object that are listed in table 1. Because a
batch of
SQL
can generate multiple
SQL
Server errors, an application needs to check
whether multiple errors have occurred. The first error in the
Errors
property will
always match the error in the
SqlExcpetion
’s properties. Listing 11 is an example.
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Server=L60\YUKON;

82
C
HAPTER
6
Error handling in SQL Server and applications
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
for (int i = 0; i < sqlex.Errors.Count; i++)
{
Console.WriteLine("Error #{0}: {1}",
i.ToString(), sqlex.Errors[i].Message);
}
}
}
}
This returns the following result:
Error #0: My Error
Error #1: Divide by zero error encountered.
Error #2: Invalid object name 'dbo.BadTable'.
In closing, let’s look at how we can handle
SQL
Server messages inside our application
code.
Handling SQL Server messages on the client
When a message is sent from
SQL
Server via a
PRINT

class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Server=L60\YUKON;
➥
Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("PRINT 'Hello'", conn);
conn.InfoMessage += new
➥
SqlInfoMessageEventHandler(conn_InfoMessage);
try
{
cmd.Connection.Open();
Listing 12 Outputting SQL Server messages
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
83
Handling SQL Server errors on the client
cmd.ExecuteNonQuery();
cmd.CommandText ="RAISERROR('An error as message', 5, 12)";
cmd.ExecuteNonQuery();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("First Error Message: " + sqlex.Message);
Console.WriteLine("Error Count: {0}",
➥
sqlex.Errors.Count.ToString());

Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand(
@"PRINT 'Printed at buffer flush'
RAISERROR('Starting', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:03';
RAISERROR('Status', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:03';
PRINT 'Done';", conn);
conn.InfoMessage += new
➥
SqlInfoMessageEventHandler(conn_ShortMessage);
Listing 13 Capturing
RAISERROR
statements
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
84
C
HAPTER
6
Error handling in SQL Server and applications
try
{
cmd.Connection.Open();
cmd.ExecuteReader();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("First Error Message: " + sqlex.Message);

RAISERROR
, the first three lines are all printed at the same
time when the
RAISERROR

WITH

NOWAIT
pushes them all to the client. This approach
can provide a convenient way to return status information for long-running tasks that
contain multiple
SQL
statements.
Summary
SQL
Server error handling doesn’t need to be an afterthought.
SQL
Server 2005 pro-
vides powerful tools that allow developers to selectively handle, capture, and consume
errors inside
SQL
Server. Errors that can’t be handled on the server can be passed
back to the application. .
NET
has specialized classes that allow applications to capture
detailed information about
SQL
Server exceptions.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

important characteristics such as integrity. But it also means using
JOIN
s, because
it’s unlikely that all the data we need to solve our problem occurs in a single table.
Almost always, our
FROM
clause contains several tables.
In this chapter, I’ll explain some of the deeper, less understood aspects of the
FROM
clause. I’ll start with some of the basics, to make sure we’re all on the same
page. You’re welcome to skip ahead if you’re familiar with the finer points of
INNER
,
OUTER
, and
CROSS
. I’m often amazed by the fact that developers the world over
understand how to write a multi-table query, and yet few understand what they’re
asking with such a query.
JOIN basics
Without
JOIN
s, our
FROM
clause is incredibly simple. Assuming that we’re using only
tables (rather than other constructs such as subqueries or functions),
JOIN
s are one
of the few ways that we can make our query more complicated.
JOIN


JOIN
looks at the two tables involved in the
JOIN
and identifies match-
ing rows according to the criteria in the
ON
clause. Every
INNER

JOIN
requires an
ON
clause—it’s not optional. Aliases are optional, and can make the
ON
clause much
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
87
JOIN basics
shorter and simpler to read (and the rest of the query too). I’ve made my life easier by
specifying
p
and
s
after the table names in the query shown below (to indicate Prod-
uct and ProductSubcategory respectively). In this particular example, the match con-
dition is that the value in one column in the first table must be the same as the
column in the second table. It so happens that the columns have the same name;
therefore, to distinguish between them, I've specified one to be from the table


JOIN
is like an
INNER

JOIN
except that rows that do not have matching values
in the other table are not excluded from the result set. Instead, the rows appear with
NULL
entries in place of the columns from the other table. Remembering that a
JOIN
is
always performed between two tables, these are the variations of
OUTER

JOIN
:

LEFT
—Keeps all rows from the first table (inserting
NULL
s for the second table’s
columns)

RIGHT
—Keeps all rows from the second table (inserting
NULL
s for the first
table’s columns)


FROM Production.Product p
LEFT JOIN
Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID;
The query in listing 2 produces results similar to the results of the last query, except
that products that are not assigned to a subcategory are still included. They have
NULL
listed for the second column. The smallest number of rows that could be returned by
this query is the number of rows in the Product table, as none can be eliminated.
Had we used a
RIGHT

JOIN
, subcategories that contained no products would have
been included. Take care when dealing with
OUTER

JOIN
s, because counting the rows
for each subcategory would return 1 for an empty subcategory, rather than 0. If you
intend to count the products in each subcategory, it would be better to count the
occurrences of a non-
NULL
ProductID or Name instead of using
COUNT(*)
. The que-
ries in listing 3 demonstrate this potential issue.
/* First ensure there is a subcategory with no corresponding products */
INSERT Production.ProductSubcategory (Name) VALUES ('Empty Subcategory');
SELECT s.Name, COUNT(*) AS NumRows


JOIN
with an
ON
clause that evaluates to true for every possible combination
of rows. The
CROSS

JOIN
doesn’t use an
ON
clause at all. This type of
JOIN
is relatively
rare, but it can effectively solve some problems, such as for a report that must include
every combination of SalesPerson and SalesTerritory (showing zero or
NULL
where
appropriate). The query in listing 4 demonstrates this by first performing a
CROSS
JOIN
, and then a
LEFT

JOIN
to find sales that match the criteria.
Listing 2 A
LEFT

OUTER

that you find around the internet should generally stay away from formatting. When it
comes to the
FROM
clause, though, I think formatting can be important.
A sample query
The Microsoft Project Server Report Pack is a valuable resource for people who use
Microsoft Project Server. One of the reports in the pack is a Timesheet Audit Report,
which you can find at http:
//msdn.microsoft.com/en-us/library/bb428822.aspx. I
sometimes use this when teaching
T-SQL
.
The main query for this report has a
FROM
clause which is hard to understand. I’ve
included it in listing 5, keeping the formatting exactly as it is on the website. In the fol-
lowing sections, we’ll demystify it by using a method for reading
FROM
clauses, and
consider ways that this query could have retained the same functionality without being
so confusing.
FROM MSP_EpmResource
LEFT OUTER JOIN MSP_TimesheetResource
INNER JOIN MSP_TimesheetActual
ON MSP_TimesheetResource.ResourceNameUID
= MSP_TimesheetActual.LastChangedResourceNameUID
ON MSP_EpmResource.ResourceUID
= MSP_TimesheetResource.ResourceUID
LEFT OUTER JOIN MSP_TimesheetPeriod
INNER JOIN MSP_Timesheet

For example, they start with one table:
FROM MSP_EpmResource
Then they
JOIN
to another table:
FROM MSP_EpmResource
LEFT OUTER JOIN MSP_TimesheetResource
ON MSP_EpmResource.ResourceUID
= MSP_TimesheetResource.ResourceUID
And they keep repeating the pattern:
FROM MSP_EpmResource
LEFT OUTER JOIN MSP_TimesheetResource
ON MSP_EpmResource.ResourceUID
= MSP_TimesheetResource.ResourceUID
INNER JOIN MSP_TimesheetActual
ON MSP_TimesheetResource.ResourceNameUID
= MSP_TimesheetActual.LastChangedResourceNameUID
They continue by adding on the construct
JOIN

table_X

ON

table_X.col

=

table_
Y.col

):
FROM MSP_EpmResource
LEFT OUTER JOIN MSP_TimesheetResource
INNER JOIN MSP_TimesheetActual
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
91
Formatting your FROM clause
ON MSP_TimesheetResource.ResourceNameUID
= MSP_TimesheetActual.LastChangedResourceNameUID
ON MSP_EpmResource.ResourceUID
= MSP_TimesheetResource.ResourceUID
Many of my students look at this part and immediately pull the second
ON
clause
(between MSP_EpmResource and MSP_TimesheetResource) and move it to before
the
INNER

JOIN
. But because we have an
INNER

JOIN
applying to MSP_Timesheet-
Resource, this would remove any
NULLs
that are the result of the
OUTER



JOIN
,” but they’re wrong.
The first
JOIN
is the
INNER

JOIN
, and this is easy to find because it’s the
JOIN
that
matches the first
ON
. To find the first
JOIN
, you have to find the first
ON
. Having found
it, you work backwards to find its
JOIN
(which is the
JOIN
that immediately precedes
the
ON
, skipping past any
JOIN
s that have already been allocated to an
ON

4
Keep going backwards from the
JOIN
to find the left side.
5
Repeat until all the
ONs
have been found.
Using this method, we can clearly see that the first
JOIN
in our sample query is the
INNER

JOIN
, between MSP_TimesheetResource and MSP_TimesheetActual. This forms
the right side of the
LEFT

OUTER

JOIN
, with MSP_EpmResource being the left side.
When the pattern can’t apply
Unfortunately for our pattern, the next
JOIN
is the
INNER

JOIN
between MSP_