使用out参数调用存储过程(Calling a stored procedure with an out parameter)

我正在尝试调用一个存储过程，其中包含一个参数和两个输出参数。

作为一个脚本，我称之为：

set @MaxPrice = 0.00;
set @MinPrice = 0.00;
set @BSku = '1011001403';
call GetSkuMinMaxPrice(@Sku,@MaxPrice, @MinPrice);
 
 我收回了我的价格 
 
 以下是我用ef5调用它的方法： 
 
decimal? minPrice;
decimal? maxPrice;

var skuParameter = new MySqlParameter("?SKU", productToUpload.Sku)
{
    Direction = ParameterDirection.Input
};
var maxPriceParameter = new MySqlParameter("?MaxPrice", SqlDbType.Decimal)
{
    Direction = ParameterDirection.Output
};
var minPriceParameter = new MySqlParameter("?MinPrice", SqlDbType.Decimal)
{
    Direction = ParameterDirection.Output
};
db.Database.ExecuteSqlCommand("call GetSkuMinMaxPrice(?SKU,?MaxPrice,?MinPrice)",
                               skuParameter, 
                               maxPriceParameter, 
                               minPriceParameter);

minPrice = minPriceParameter.Value as decimal?;
maxPrice = maxPriceParameter.Value as decimal?;
 
 对我来说，这看起来很好，但我从MySQL服务器收到此错误消息： OUT or INOUT argument 2 for routine tng.GetSkuBaseMinMaxPrice is not a variable or NEW pseudo-variable in BEFORE trigger 。 
 
 那么，除了不使用Entity Framework之外 ，我还需要做些什么来实现这项工作呢？ 
 
 到目前为止我的一些研究： 
 
 
  除out参数外的所有内容的语法  
  MySQL中可能存在的错误  
I'm trying to call a stored procedure that has one in parameter and two out parameters.
 
As a script, I call it like this:
 
set @MaxPrice = 0.00;
set @MinPrice = 0.00;
set @BSku = '1011001403';
call GetSkuMinMaxPrice(@Sku,@MaxPrice, @MinPrice);
 
and I get back my prices
 
Here's what I have for calling this with ef5:
 
decimal? minPrice;
decimal? maxPrice;

var skuParameter = new MySqlParameter("?SKU", productToUpload.Sku)
{
    Direction = ParameterDirection.Input
};
var maxPriceParameter = new MySqlParameter("?MaxPrice", SqlDbType.Decimal)
{
    Direction = ParameterDirection.Output
};
var minPriceParameter = new MySqlParameter("?MinPrice", SqlDbType.Decimal)
{
    Direction = ParameterDirection.Output
};
db.Database.ExecuteSqlCommand("call GetSkuMinMaxPrice(?SKU,?MaxPrice,?MinPrice)",
                               skuParameter, 
                               maxPriceParameter, 
                               minPriceParameter);

minPrice = minPriceParameter.Value as decimal?;
maxPrice = maxPriceParameter.Value as decimal?;
 
To me, this looks fine, but I get this error message from the MySQL server: OUT or INOUT argument 2 for routine tng.GetSkuBaseMinMaxPrice is not a variable or NEW pseudo-variable in BEFORE trigger.
 
So, what do I need to do to make this work, short of not using Entity Framework?
 
Some of my research so far:
 
 
 Syntax for everything except out parameters 
 Possible bug in MySQL 

                
最满意答案
                
                    
                        
 这似乎是MySQL处理out参数的结果。 我的解决方法是更改​​存储过程以返回out参数的select查询，创建一个POCO，其公共属性名称与存储过程选择结果的列名称匹配。 
 
 新存储过程调用 
 
set @BSku = '1011001403';
call GetSkuPrices(@Sku);
 
 我的POCO： 
 
private class PriceOutput
{
    public decimal? MaxPrice { get; set; }
    public decimal? MinPrice { get; set; }
}
 
 我的主叫代码： 
 
decimal? minPrice = null;
decimal? maxPrice = null;

var skuParameter = new MySqlParameter("?SKU", productToUpload.Sku);
var basePrices = db.Database.SqlQuery<PriceOutput>("call GetSkuPrices(?SKU)",                                                       
                                                   skuParameter).FirstOrDefault();
if (basePrices != null)
{
    minPrice = basePrices.MinPrice;
    maxPrice = basePrices.MinPrice;
}
It seems that this is a consequence of MySQL handling of out parameters. My workaround was to change the stored procedure to return a select query of the out parameters, create a POCO with public property names matching the stored procedure select result's column names.
 
New stored Procedure call
 
set @BSku = '1011001403';
call GetSkuPrices(@Sku);
 
My POCO:
 
private class PriceOutput
{
    public decimal? MaxPrice { get; set; }
    public decimal? MinPrice { get; set; }
}
 
My calling code:
 
decimal? minPrice = null;
decimal? maxPrice = null;

var skuParameter = new MySqlParameter("?SKU", productToUpload.Sku);
var basePrices = db.Database.SqlQuery<PriceOutput>("call GetSkuPrices(?SKU)",                                                       
                                                   skuParameter).FirstOrDefault();
if (basePrices != null)
{
    minPrice = basePrices.MinPrice;
    maxPrice = basePrices.MinPrice;
}