CODEWITHSUNDEEP
c#mysqlentity-framework-5
Christopher Stevenson
Christopher Stevenson

Reputation: 2861

Calling a stored procedure with an out parameter

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:

Upvotes: 4

Views: 3276

Answers (2)

Luke
Luke

Reputation: 23680

Rather than attempting to use OUT parameters, I believe that these are not supported by MySQL for EF, return the values that you wanted to return from the PROCEDURE using a SELECT statement.

This is because each select statement that doesn't select INTO will be returned as a result set from the procedure. I found this out here.

I wanted to return the number of affected rows from my stored proc, so I removed my out parameters and put this in it's place:

SELECT ROW_COUNT() AS Success;

I then created the following object to catch the value:

public class UsePasswordResult
{
    public int Success { get; set; }
}

Then I got the values using:

// Set values to desired parameter values
int quizId = 1;
string password = "mypassword";

UsePasswordResult result = this.Context.Database.SqlQuery<UsePasswordResult>("CALL UsePassword({0}, {1})", quizId, password).FirstOrDefault();

I hope that this helps someone! I'm using EF6 and it works.

Upvotes: 0

Christopher Stevenson
Christopher Stevenson

Reputation: 2861

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;
}

Upvotes: 4

Related Questions