mysql stored-procedure out parameter

Question

I have a mysql stored procedure from this  google book   and one example is this   DELIMITER     DROP PROCEDURE IF EXISTS my sqrt   CREATE PROCEDURE my sqrt input number INT  OUT out number FLOAT  BEGIN     SET out number SQRT input number   END    DELIMITER     The procedure compiles fine   I am using MySQL Query Browser in ubuntu    However when I call the procedure   CALL my sqrt 4  out value      also in query browser   It returns an error    1064  check the manual that correspond to the      Why isn t this example working

User · Answer

I know this is an old thread  but if anyone is looking for an answer of why their procedures doesn t work in the workbench and think the only result is  Query canceled  or anything like that without clues   the output with errors or problems is hiddenl  I do not know why  I do understand it s annoying  but it is there  just move your cursor above the line above the message  it will turn in an double arrow  up and down  you can then click and drag that line up  then you will see a console with the message you missed

User · Answer

I just tried to call a function in terminal rather then MySQL Query Browser and it works  So  it looks like I m doing something wrong in that program     I don t know what since I called some procedures before successfully  but there where no out parameters      For this one I had entered  CALL my sqrt 4  out value   SELECT  out value    And it results with an error      You have an error in your SQL syntax    check the manual that corresponds to   your MySQL server version for the   right syntax to use near  SELECT    out value  at line 2   Strangely  if I write just   CALL my sqrt 4  out value      The result message is   Query canceled   I guess  for now I will use only terminal

User · Answer

SET out number SQRT input number       Instead of this write   select SQRT input number      Please don t write SET out number and your input parameter should be   PROCEDURE  test   my sqrt  IN input number INT  OUT out number FLOAT

User · Answer

Unable to replicate   It worked fine for me   mysql gt  CALL my sqrt 4   out value   Query OK  0 rows affected  0 00 sec   mysql gt  SELECT  out value   ------------     out value    ------------    2              ------------  1 row in set  0 00 sec    Perhaps you should paste the entire error message instead of summarizing it

User · Answer

try changing OUT to INOUT for your out number parameter definition     CREATE PROCEDURE my sqrt input number INT  INOUT out number FLOAT   INOUT means that the input variable for out number   out value in your case   will also serve as the output variable from which you can select the value from

User · Answer

If you are calling from within Stored Procedure don t use    In my case it returns 0   CALL SP NAME L OUTPUT PARAM

User · Answer

You must have use correct signature for input parameter  IN is missing in the below code   CREATE PROCEDURE my sqrt IN input number INT  OUT out number FLOAT

[mysql] mysql stored-procedure: out parameter

Examples related to mysql

Examples related to stored-procedures