如何从存储过程中获取输出参数结果和查询结果

Question

我有这个存储过程

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

我使用DbContext 使用实体框架核心从C#调用

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

当我从代码中调用SP时，输出参数为空。如何在代码中获取输出参数值

---

当我直接从SQL调用SP时，输出参数会正确填充。

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 1

在SQL文本中构建参数时缺少OUTPUT关键字：

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

应为：

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

您可以通过修改将参数添加到SQL文本的行来解决此问题：

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