THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Catching multiple exceptions on the client is robust and easy

Maria Zakourdaev has just demonstrated that if our T-SQL throws multiple exceptions,
ERROR_MESSAGE() in TRY..CATCH block will only expose one.

When we handle errors in C#, we have a very easy access to all errors.

The following procedure throws two exceptions:

CREATE PROCEDURE dbo.ThrowsTwoExceptions
AS
  BEGIN
;
    
RAISERROR('Error 1', 16, 1) ;
    
RAISERROR('Error 2', 16, 1) ;
  
END ;
GO

EXEC dbo.ThrowsTwoExceptions ;

Both exceptions are shown by SSMS:

Msg 50000, LEVEL 16, State 1, PROCEDURE ThrowsTwoExceptions, Line 4
Error 1
Msg 50000
, LEVEL 16, State 1, PROCEDURE ThrowsTwoExceptions, Line 5
Error 2

The following C# code shows how easily we can access both exceptions:

try
{
    var command
= connection.CreateCommand();
    
command.CommandText = "dbo.ThrowsTwoExceptions";
    
command.CommandType = CommandType.StoredProcedure;
    
command.ExecuteNonQuery();
}
catch(SqlException e)
{
    Console.WriteLine
(e.ToString());
    
foreach (var error in e.Errors)
    
{
        var exception
= error as SqlError;
        
if(exception != null)
        
Console.WriteLine(string.Format("Number: {0} Message: {1}", exception.Number, exception.Message));
    
}
}

Its output is as follows:

Number: 50000 Message: Error 1
Number: 50000 Message: Error 2

As we have seen, catching more than one exception is easy if we use C#.

Published Wednesday, June 06, 2012 1:30 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Not if your exceptions are batch aborting -- I use SET XACT_ABORT ON to avoid this particular scenario. (Doesn't help with Maria's case.)

June 6, 2012 4:07 PM
 

Alexander Kuznetsov said:

Adam,

I am not sure what do you mean. I think Errors collection exposes all errors regardless of XACT_ABORT setting on the server side. Also XACT_ABORT does not guarantee that you will only get one error. See for yourself:

ALTER PROCEDURE dbo.ThrowsTwoExceptions

AS

 BEGIN ;

   SET XACT_ABORT ON ;

   BEGIN TRANSACTION ;

   RAISERROR('Error 1', 16, 1) ;

   RAISERROR('Error 2', 16, 1) ;

   COMMIT ;

 END ;

GO

EXEC dbo.ThrowsTwoExceptions ;

June 6, 2012 4:48 PM
 

Adam Machanic said:

Alex,

Not if you're using RAISERROR, no. RAISERROR (non connection-aborting) is not considered by XACT_ABORT to be an error. Yet another pitfall!

June 6, 2012 4:53 PM
 

Alexander Kuznetsov said:

Adam,

Can you do me a favor and provide an example when Errors collection fails to provide some error(s) ?

If such examples exist, that means I have a loophole in my error handling - I'd be happy to learn it and improve.

I know that error handling in T-SQL is very problematic to put it mildly. I don't use it whenever I can.

June 6, 2012 5:02 PM
 

Adam Machanic said:

Alex,

I think you misunderstood me. I use XACT_ABORT to make sure that my modules bail out the moment that there is even a single error. I don't want to give anything the chance to hit two errors -- who knows what will happen in the interim? The issue is that RAISERROR is not considered to be an error by XACT_ABORT. In other words, RAISERROR will not abort the batch. But it is considered to be an error by TRY/CATCH.

June 6, 2012 7:44 PM
 

Uri Dimant said:

June 7, 2012 1:14 AM
 

Alexander Kuznetsov said:

Uri,

Of course I have read Erland's articles. However, it is so very much easier to handle errors in C#. In fact, in C# universe there is no equivalent to Erland's articles - everything just work without gotchas, simple and reliable, so there is no need to write it up.

June 7, 2012 1:51 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement