PROCEDURE can't return a result set in the given context

[zippo]

I have a problem when calling a procedure in MySQL 5...

When executin the "Call MYPROC(PAR1,PAR2,PAR3)" from MySQL Query Browser it works OK, altrough I noticed that it returns data - it shouldn't.

But when executing from my application i get this error

---------------------------
Debugger Exception Notification
---------------------------
Project Prodaja6.exe raised exception class EZSQLException with message 'SQL Error: PROCEDURE prodaja.RECALC_DOK_VREDNOST can't return a result set in the given context'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------

Why??? I just want to modify a value in one field. I think the error is somehow related to the usage of user defined variables (the @vrednost_dok). BTW: I call all the procedures the same way and it works OK - before editing this procedure also worked OK. The entire procedure is here:

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `prodaja`.`RECALC_DOK_VREDNOST` $$
CREATE PROCEDURE `RECALC_DOK_VREDNOST`(IN INPUT_TIP Int, IN INPUT_ID Int, IN INPUT_IZHOD Char(1))
    MODIFIES SQL DATA
    COMMENT 'Izračun vrednosti dokumenta'
BEGIN
  If INPUT_IZHOD="D" Then
    Select @VREDNOST_DOK := Sum(PC*KOL*(1+DDV/100)) As VRED_PC
    From IZH_POSTAVKE
    Where DOK_TIP=INPUT_TIP And DOK_ID=INPUT_ID
    Group By DOK_TIP,DOK_ID;

    Update IZH_DOK Set VREDNOST=@VREDNOST_DOK Where TIP_DOK=INPUT_TIP And ID=INPUT_ID;
  End If;
END $$

DELIMITER ;

Does anyone know where's the point?

[mdaems]

What does the code do when called from mysql console? Does it also return an error message? Or does it return data?

Mark

[zippo]

From console: Call RECALC_DOK_VREDNOST(1,100,"N")

The procedure shouldn't return any data, but I get a float result (the value of the select query). I'm still working on it, but I think is because the Select statement - maybe I should use cursors?

[mdaems]

Yes, For mysql a query in proc can return a resultset (unlike in Oracle and other databases). Cursors wil do the trick, I suppose. Not yet worked with stored procs so I only conclude this having reading the docs once.
But, lookin the mysql stored proc documentation. There's a 'select ... into' syntax as well.
This will be the best solution if the query is only executed once (otherwise cursors could be better). In Oracle these are the only 2 ways to do it.

Mark

[zippo]

Great! It looks just what I needed! Strange I didn't found it in the documentation - I browsed it for at leas 2 hrs..