Is it possible to get both the RAISEERROR message and the return value from a stored procedure in .NET?

206 Views Asked by At

I have this procedure that does a RAISEERROR and a return @tmp_cnt at the end. This RAISEERROR doesn't stop the procedure from executing as it should return the tmp_cnt as well. I use this in .NET and my code goes into the catch (SqlException e) part so this tmp_cnt doesn't get returned. This is the code for it

(string, int) result;
result.Item1 = null;
result.Item2 = -1;

try {
  result.Item2 = await _context.Database.ExecuteSqlRawAsync("EXECUTE core.STORED_PROCEDURE", params);
} catch (SqlException e) {
  foreach(SqlError error in e.Errors) {
    if (error.Class > 10) {
      result.Item1 = error.Message;
    }
  }
}

This way, I only get the error.Message while the result.Item2 remains -1 and I'm aware that this is a normal thing to do as this is what it should do. If I remove the try/catch part, the app throws an exception and code 500. The question I have is, is there a way to get both the RAISEERROR and the return from a stored procedure in .NET? This is the SQL part

IF @tmp_cnt < @ent_cnt
BEGIN
DECLARE @msg AS NVARCHAR(MAX) = CONCAT('Not all of the selected entities are eligible for change. Will be changed for ',
  CAST(@tmp_cnt AS NVARCHAR(50)), ' out of the selected ', CAST(@ent_cnt AS NVARCHAR(50)), ' entities.')
RAISERROR(@msg, 15, 1)
RETURN @tmp_cnt;
END

If not possible, have you ever stumbled upon a scenario like this and is there a workaround for it?

0

There are 0 best solutions below