How to call a stored procedure in SQL Server with output parameter from Web API

I do not know how to upgrade this code to retrieve output parameter from a stored procedure. For example that parameter name is TransactionPassCorrectly (it is a return parameter for some calculation in database and its type is int)

public async Task<ActionResult<int>> UpdateSuperHeroFromSP(int SuperHeroId, string Place)
{
    return await context.Database.ExecuteSqlRawAsync($"UpdateSuperHero {SuperHeroId},{Place}");
}

You are't passing parameters there, you are injecting them, which is dangerous as it can lead to malicious abuse and/or incorrect results. It also doesn't work at all with output parameters.

Instead you need to pass parameters properly (using SqlParameter) or you can use ExecuteSqlInterpolated which can handle string interpolation without injecting. You then need to pick up the value off the parameter object.

Note that the parameter in the EXEC statement needs the OUTPUT keyword afterwards.

You should also not rely on parameter order and instead specify the parameter names explicitly.

public async Task<ActionResult<int>> UpdateSuperHeroFromSP(int SuperHeroId, string Place)
{
    var SuperHeroIdParam = new SqlParameter("@SuperHeroId", SqlDbType.Int) { Value = SuperHeroId };
    var PlaceParam = new SqlParameter("@Place", SqlDbType.NVarChar, 100) { Value = Place };
    var TransactionPassCorrectlyParam = new SqlParameter("@TransactionPassCorrectly", SqlDbType.Int) { Direction = ParameterDirection.Output };

    await context.Database.ExecuteSqlRawAsync(@"
EXEC UpdateSuperHero
  @SuperHeroId = @SuperHeroId,
  @Place = @Place,
  @TransactionPassCorrectly = @TransactionPassCorrectly OUTPUT;
",
        new object[] {SuperHeroIdParam, PlaceParam, TransactionPassCorrectlyParam });

    return (int)TransactionPassCorrectly.Value;
}

public async Task<ActionResult<int>> UpdateSuperHeroFromSP(int SuperHeroId, string Place)
{
    var TransactionPassCorrectlyParam = new SqlParameter("@TransactionPassCorrectly", SqlDbType.Int) { Direction = ParameterDirection.Output };
    await context.Database.ExecuteSqlInterpolatedAsync(@$"
EXEC UpdateSuperHero
  @SuperHeroId = {SuperHeroId},
  @Place = {Place},
  @TransactionPassCorrectly = {TransactionPassCorrectlyParam} OUTPUT;
");

    return (int)TransactionPassCorrectly.Value;
}

If the parameter could be NULL then you need return TransactionPassCorrectly.Value as int; and you need to declare your function Task<ActionResult<int?>>.

Note that the return value of ExecuteSql is not the RETURN of a procedure (which you shouldn't really use anyway), it's just the number of rows modified if any. For return values you would need ParameterDirection.ReturnValue.