Question
My stored procedure is throwing custom errors to handle validation within a multi user web app. This is working as expected in SQL Server with error number 50001 being returned however when my C# code catches the error it always has the error number 50000 instead of 50001 therefore I cannot treat the errors differently.
How can I catch error number 50001? Note that I am using Dapper to execute all stored procedures.
SQL
THROW 50001, 'Client already has an Active Visit! THIS IS DEV!!!!',1;
Msg 50001, Level 16, State 1, Line 1
Client already has an Active Visit! THIS IS DEV!!!!
C#
catch (SqlException ex)
{
var errorHandler = new ErrorHandler();
var msg = errorHandler.ErrorMessage(ex);
if (ex.Number == 50001)
{
return BadRequest(msg);
}
else
{
return StatusCode(500, msg);
}
}
catch (Exception ex)
{
var errorHandler = new ErrorHandler();
return StatusCode(500, errorHandler.ErrorMessage(ex));
}
EXAMPLE
Accepted Answer
SQL server error 50000 is reserved for general user defined message, so it sounds like it's not finding error 50001 in sys.messages. You can try to add the error using the stored procedure sp_addmessage.
Once that is done, you can call it like this:
RAISERROR(50001, 1, 1)
Another reason it could be error 50000 is if you are raising the SQL error in a SQL TRY/CATCH as that will always return a 50000 error code.
Just tried this code myself:
using (var cmd = new SqlCommand("THROW 50001,'error',1;", conn))
{
cmd.ExecuteNonQuery();
}
And that does return ex.Number 50001 so it must be a problem in your SQL. Like I said above, it could be caused by a TRY/CATCH as this won't save the original error code number.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow