c# - MySQL 存储过程的奇怪行为

Question

我在 mysql 中有一个存储过程，定义如下

DROP PROCEDURE IF EXISTS subway.cust_inventory;
CREATE PROCEDURE subway.`cust_inventory`(IN lastItem INT,IN lastValue INT)
BEGIN
delete from temp_inventory;
drop table   temp_inventory;
create table  temp_inventory as
SELECT CONCAT ('390',
    cast(DATE (t6.InventoryDate) as char)
    ,cast(t6.SKUorItem as char)
    ) AS PK,t6.CustomerID, t6.entityID, t6.inventoryDate, t6.SKUorItem,      t6.Category, t6.inventory FROM
(
SELECT
'3' as CustomerID,
'90' as entityID,
t1.InventoryDate as inventoryDate, 
t1.idItem as SKUorItem,
t3.categoryInventary as Category,
@lastValue :=  if( @lastItem = t1.idItem, @lastValue + ifnull(t1.buyQty,0),    ifnull(t1.buyQty,0) ) as inventory,
@lastItem := t1.idItem
FROM
(
select InventoryDate, idItem, ifnull(sum(buyqty),0) as buyqty FROM(
(SELECT date(date) as InventoryDate, idItem, ifnull(Sum(movedQuantity),0) as buyqty
FROM subway.StockMovement
GROUP BY idItem, date(date)
)
UNION
(SELECT date(date) as InventoryDate, idItem,  ifnull(Sum(initialBuyQuantity),0) as buyqty
FROM subway.InvoiceStock where type = 'buy'  GROUP BY idItem, date(date))

UNION
 (select date(date) as inventorydate, idItem,ifnull(-sum(quantity),0) as  buyQty 
from subway.saleitem
group by idItem, date(date)
)
) t5
group by inventorydate,idItem
) t1
LEFT JOIN subway.item as t2 ON t1.iditem = t2.iditem
LEFT JOIN subway.CategoryInventary as t3 on t2.idCategoryInventary =    t3.idCategoryInventary
order by t1.iditem, t1.InventoryDate
) t6 where t6.inventorydate = '2015-06-09' order by inventoryDate,SKUorItem
;
END;

此过程在 temp_inventory 表中插入 72 条记录这在我从命令提示符或从 toad for mysql 调用过程时效果很好

但是当我按照下面指定的方式从 C# 代码调用相同的过程时

mySQLConn.GetConnection();
            MySqlCommand cmd = new MySqlCommand("cust_inventory", mySQLConn.Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("lastItem", 0));
            cmd.Parameters.Add(new MySqlParameter("lastValue", 0));
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            MySqlDataReader myreader = new MySqlCommand("Select * from temp_inventory",mySQLConn.Conn).ExecuteReader();

这会在列 list (最后一列)中给出错误的值

知道为什么会这样

Answer 1

试试下面的代码。 0 在作为参数值传递时在 MySql 中以不同的方式处理。

cmd.Parameters.Add(new MySqlParameter("lastItem",MySqlDbType.Int32).Value=0);
cmd.Parameters.Add(new MySqlParameter("lastValue", MySqlDbType.Int32).Value=0);

无需外部调用 cmd.Dispose(); .Net Clr 将在内部处理。

您只是将 0 值传递给这些参数，因此只需在存储过程中创建两个变量并将值设置为 0。无需在存储过程中创建任何参数，因此无需从 c# 中传递它们 代码。

SET @lastValue=0, @lastItem=0;