Thread: [Firebird-net-provider] Trouble retrieving stored procedure result with ExecuteStoreQuery

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

I have a stored procedure in my database that returns a scalar value (a
double). I am trying to figure out how to get the results via
ExecuteStoreQuery, using Entity Framework. Here is my c# code:

var db = new MyEntities();
var oc = ((IObjectContextAdapter)db).ObjectContext;
string myquery = "execute procedure PREDICT_COST_1 ('12345',1)";
var result = oc.ExecuteStoreQuery<double>(myquery);
double thecost = result.First<double>();

If I set a breakpoint before the last line and inspect the result variable,
it is an empty ObjectResult. So, the last line raises an exception when I
call First() because it is empty.

I know that the stored procedure should be returning a value, because if I
execute it directly against the database with the same parameters, it
returns the correct value.

Here's the source for my stored procedure:

create procedure PREDICT_COST_1 (BARCODE VarChar(25), STOREID INTEGER)
returns (THE_COST Double Precision) as
DECLARE VARIABLE TEMP_COST Double Precision;
BEGIN
select first 1 itemcost from orderitems A inner join orders B on A.orderid
= B.orderid
where A.itembarcode = :BARCODE and A.itemqtyordered > A.itemqtyreceived
and A.itemcost > 0 order by B.orderdate desc into :TEMP_COST;
if (TEMP_COST is null) then
BEGIN
select first 1 A.vpcost from vendorproducts A inner join products B
on A.VPUPC = B.PRODBARCODE and A.VPVENDOR = B.PRODSOURCE
where B.prodbarcode = :BARCODE and A.STOREID = :STOREID and A.vpcost > 0
into :TEMP_COST;
if (TEMP_COST is null) then
BEGIN
select first 1 vpcost from vendorproducts
where vpupc = :BARCODE and STOREID = :STOREID and vpcost > 0 into
:TEMP_COST;
END
END
THE_COST = TEMP_COST;
END

What am I doing wrong?

-Joe

Thread: [Firebird-net-provider] Trouble retrieving stored procedure result with ExecuteStoreQuery

A powerful, cross platform, SQL database system

firebird-net-provider