CODEWITHSUNDEEP
mysqlstored-procedures
domagojk
domagojk

Reputation: 1060

mysql stored-procedure: out parameter

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) 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

Why isn't this example working?

Upvotes: 41

Views: 215437

Answers (7)

ship shuk - www.shipshuk.com
ship shuk - www.shipshuk.com

Reputation: 57

If you are calling from within Stored Procedure don't use @. In my case it returns 0

CALL SP_NAME(L_OUTPUT_PARAM)

Upvotes: 2

Maurice
Maurice

Reputation: 7371

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.

Upvotes: 0

Noby Nirmal
Noby Nirmal

Reputation: 349

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)

Upvotes: 1

user4022749
user4022749

Reputation:

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)

Upvotes: 8

SomeOne_1
SomeOne_1

Reputation: 1002

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!

Upvotes: 3

domagojk
domagojk

Reputation: 1060

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...

Upvotes: 6

chaos
chaos

Reputation: 124277

Unable to replicate. It worked fine for me:

mysql> CALL my_sqrt(4, @out_value);
Query OK, 0 rows affected (0.00 sec)

mysql> 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.

Upvotes: 52

Related Questions