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#.