1

I have a stored procedure in SQL Server that throws an error whenever a condition is hit. In order to catch this error and display it to the user, I use

try 
{
   //code
} 
catch (Exception e)
{
   return BadRequest(e.Message);
}

This catches most of the cases but on the other hand, this procedure has some print messages that also get caught by this Exception. Is there a way to catch the exception thrown only from RAISERROR and ignore this print message from SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthino Russo
  • 59
  • 1
  • 8
  • 3
    `PRINT` never produces exceptions. What is probably happening is that, when a message with a high severity is raised, the earlier lower-severity `PRINT` messages get rolled into the error. You should be able to distinguish and filter these by catching `SqlException` specifically and using the `Errors` property. Conceivably, you might also be able to pre-emptively filter them by hooking up an event handler to `SqlConnection.InfoMessage` (disclaimer: not tested, and this may not do anything due to buffering). – Jeroen Mostert Dec 03 '21 at 13:01
  • `print` messages from SQL Server shouldn't be generating exceptions, not if you're just using the ADO.Net objects (`SqlConnection`, `SqlCommand`, etc). So if you're getting exceptions, it's either extra code working with those objects turning the messages into exceptions, or it's some other library/stack being used that does this. None of which we can see in `//code`. – Damien_The_Unbeliever Dec 03 '21 at 13:01
  • Look at: https://stackoverflow.com/a/22126876/5967360 – Mucahid Uslu Dec 03 '21 at 13:04
  • 1
    Does this answer your question? [Need to get the SQL Server "PRINT" value in C#](https://stackoverflow.com/questions/5749826/need-to-get-the-sql-server-print-value-in-c-sharp) – Charlieface Dec 03 '21 at 13:21
  • Are you actually using RAISERROR to "print" messages immediately? – SMor Dec 03 '21 at 23:02

1 Answers1

3

All info and error messages generated during command execution are buffered and available when a SqlException is caught. The Message property includes the text of all info messages (print statements and errors with a severity of less than 11) and the warnings/errors that caused the exception.

To ignore info messages, use the SqlException Errors collection and process only those with severity (SqlError.Class property) of 11 or higher:

catch (SqlException e)
{
   var sb = new StringBuilder();
   foreach(var error in e.Errors)
   {
       if(error.Class > 10) sb.AppendLine(error.message);
   }
   return BadRequest(sb.ToString());
}
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71