CODEWITHSUNDEEP
mysqlstored-procedures
richardaum
richardaum

Reputation: 6777

Cascading stored procedures in MySQL

The question is relatively simple.

Is possible to call a procedure in MySQL, into another?

Example:

There are two procedures created: proc1 and proc2

Within the proc1, I call proc 2. Thus:

BEGIN

IF ( CALL ConsultConsoleAvailableTime(`StartTime`, `PlusTime` ) IS NULL ) THEN
    SELECT 'error';
END IF;

END;

Is it possible? How? Did not work here not. :D

Edit1:

1318 - Incorrect number of arguments for PROCEDURE sistemajogosxbox.ConsultConsoleAvailableTime; expected 3, got 2

Edit2:

1172 - Result consisted of more than one row

And now?

Upvotes: 0

Views: 1655

Answers (2)

Nonym
Nonym

Reputation: 6299

Just to illustrate one way to use a stored procedure within another:

DELIMITER //
CREATE PROCEDURE setOutputParameter (
    OUT OutputParameter VARCHAR(20)
)
BEGIN

    SELECT 'output' INTO OutputParameter;

END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE useOutputParameter ()
BEGIN
    CALL setOutputParameter(@tmpOutputParameter);

    IF @tmpOutputParameter = 'output' 
        THEN SELECT 'string: output was returned' AS res;
    END IF;

END//
DELIMITER ;

Procedure setOutputParameter sets a value to its out parameter, and then that value is created a placeholder for in procedure setOutputParameter (and into variable: @tmpOutputParameter which is then checked in the IF..THEN.. statement.

Again, this may not be the simplest example and might even be overkill for procedures (use functions as a possible alternative), and there are other ways to return values to be used within a stored procedure that aims to utilize the output of one stored procedure in it.

[edit] Based on post comment:

An INOUT or OUT parameter (only available in stored procedures, and not functions) inside a stored procedure allows the caller of the stored procedure via CALL storedprocedurename.. command to pass a user defined variable to the out parameter's position of the called procedure--in which that same procedure runs and passes a value to it. After the called procedure executes, the out value, passed into the user-defined variable becomes available. In the example above, the out parameter OutputParameter in stored procedure setOutputParameter will require a variable to be passed to it upon being called, because the stored procedure setOutputParameter will pass a value to it. So, in effect, once the code inside stored procedure useOutputParameter calls if via CALL setOutputParameter(@tmpOutputParameter);, the variable it passed to act as an out parameter: @tmpOutputParameter will now contain the string value output which the rest of the code within procedure useOutputParameter can use...

Read more here: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Upvotes: 2

Hammerite
Hammerite

Reputation: 22340

You can call one stored procedure from another. I have done it before.

I think your example is dodgy because proc2() doesn't have a return value, like a function would. Perhaps you will need to make proc2() a function instead of a procedure.

Upvotes: -1

Related Questions