1

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?

anthino12
  • 770
  • 1
  • 6
  • 29
  • Why not just pass few values without raising an exception, and then process them accordingly? – Anton Grig Dec 22 '21 at 09:46
  • What do you mean by `pass few values`? If that's the case, how would I get this message? – anthino12 Dec 22 '21 at 09:48
  • Does this answer your question? [Calling stored procedure with return value](https://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value) – Charlieface Dec 22 '21 at 09:52
  • Side point: you should use `THROW` rather than `RAISERROR` – Charlieface Dec 22 '21 at 09:53
  • Don't use the return value (it's limited to `INT` values anyway), use an output parameter. If you set the output parameter *before* producing an error, it will be available in the calling code. (At least, this is true on the ADO.NET level -- depending on how `ExecuteSqlRawAsync` rolls, it may not be true for Entity Framework.) – Jeroen Mostert Dec 22 '21 at 09:55
  • It should return an `INT`, I don't have problems with it. Whenever this `RAISEERROR` doesn't get thrown up, I get the right value. However when the error gets invoked, I can't get the value. @JeroenMostert. Anyway, will it make a big difference if I do so? @Charlieface – anthino12 Dec 22 '21 at 09:58
  • @anthino12 I meant something like [this](https://stackoverflow.com/questions/18901545/return-result-from-select-query-in-stored-procedure-to-a-list). – Anton Grig Dec 22 '21 at 10:04
  • @AntonGrig Hm, I see. I mostly work with Entity framework but will have a look into your example. It's worth reading, thank you for your time :) – anthino12 Dec 22 '21 at 10:05

0 Answers0