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

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
 

ghasem said:

hi guy's

i use this code :

ELSE IF (@Name = @N)

  RAISERROR('Name is Exists',16,1)

  ELSE IF (@User_Name = @U)

  RAISERROR('User_Name is Exists',16,1)

  ELSE IF (@User_Pass = @UP)

  RAISERROR('User_Pass is Exists',16,1) RETURN

and in C# project use this :

catch(SqlException e)

{

   MessageBox.Show(e.ToString());

   foreach (var error in e.Errors)

   {

       var exception = error as SqlError;

       if(exception != null)

       MessageBox.Show(string.Format("Number: {0} Message: {1}", exception.Number, exception.Message));

   }

}

but only sen frist error !

how do right ?

September 18, 2015 10:15 AM
New Comments to this post are disabled

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

Privacy Statement