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));
}
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.