How to get output parameter results and query result from stored procedure?

I have this stored procedure

CREATE PROCEDURE UpdateWeatherReport (
    @TemperatureData VARCHAR(100),
    @StationID VARCHAR(7),
    @ErrorCode INT OUTPUT,
    @ErrorMessage VARCHAR(200) OUTPUT
)
AS
BEGIN

        SET @ErrorCode=12;
        SET @ErrorMessage='test error message';

        DECLARE @PredictionValid BIT = 'true' 
        DECLARE @ExpiryDays TINYINT = 2
        
        SELECT 
        @PredictionValid AS [PredictionValid]
        ,@ExpiryDays AS [ExpiryDays]
END

which I call from C# using Entity Framework Core using a DbContext

 var pTemperatureData = new SqlParameter("@TemperatureData", System.Data.SqlDbType.VarChar) { Value = "...", Direction = System.Data.ParameterDirection.Input };
 var pStationID = new SqlParameter("@StationID", System.Data.SqlDbType.VarChar) { Value = "...", Direction = System.Data.ParameterDirection.Input };
 var pErrorCode = new SqlParameter("@ErrorCode", System.Data.SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
 var pErrorMessage = new SqlParameter("@ErrorMessage", System.Data.SqlDbType.Int, size: 200) { Direction = System.Data.ParameterDirection.Output };

 var sql = $"EXECUTE {name} ";
 parameters.ForEach(p => sql += $"{p.ParameterName},");
 sql = sql.TrimEnd(',');
 
 //"EXECUTE UpdateWeatherReport @TemperatureData, @StationID, @ErrorCode, @ErrorMessage"

await Context.Database.ExecuteSqlRawAsync(sql, parameters.ToArray());

pErrorCode.Value; <-------------------- empty after call to stored procedure
pErrorMessage.Value; <----------------- empty after call to stored procedure

When I call the SP from code, the output parameters are empty. How to get the output parameter values in code?

When I call the SP directly from SQL the output parameters are populated correctly.

DECLARE @TemperatureData VARCHAR(100)='abc 123 def 456 ...';
DECLARE @StationID VARCHAR(7)='19372';
DECLARE @ErrorCode INT;
DECLARE @ErrorMessage VARCHAR(200);

EXEC UpdateWeatherReport  @ParcelBarcode, @StationID, @ParcelStatus, @DateDelivered, @PickupCode, @ErrorCode OUTPUT, @ErrorMessage OUTPUT;

Answer

You’re missing the OUTPUT keyword when building the parameters in the SQL text:

EXECUTE UpdateWeatherReport @TemperatureData, @StationID, @ErrorCode, @ErrorMessage

Should be:

EXECUTE UpdateWeatherReport @TemperatureData, @StationID, @ErrorCode OUTPUT, @ErrorMessage OUTPUT

You can fix that by modifying the line where you add the parameters to the SQL text:

parameters.ForEach(p => sql += $"{p.ParameterName}{(p.Direction == System.Data.ParameterDirection.Output ? " OUTPUT" : string.Empty)},");

Advertisement

Answer