MySQL calling a stored proc from another stored proc.

Question

I am working on a stored proc (parent) that calls another stored proc (child). The child proc returns a record set with 1 row every time.

What I need to do is pull the data from the child proc and use it in the parent proc. Using methodology from MSSQL I would assume I could just populate a temp table, but I am not quite sure how to do this.

Any help with this is greatly appreciated.

Here is the current version of my proc.

DELIMITER//

CREATE PROCEDURE CreateTransaction(IN p_TransType tinyint, in p_UserID INT)

BEGIN  

DROP TEMPORARY TABLE IF EXISTS fileData_tmp;

CREATE TEMPORARY TABLE fileData_tmp (t_FilePrefix varchar(5), t_FileSuffix int, t_FileDate varchar(4));

CALL GenerateFileNumber(p_TransType);


END//
DELIMITER;

Answer 1

If you're trying to return a value, why not use a function instead of a procedure?

DROP TABLE IF EXISTS Filename;
CREATE TABLE Filename(
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO Filename(name) VALUES ('AAA'), ('BBB'), ('CCC'), ('DDD'), ('EEE');

DELIMITER //
DROP FUNCTION IF EXISTS GenerateFileNumber//
CREATE FUNCTION GenerateFileNumber(p_name VARCHAR(255))
RETURNS BIGINT
COMMENT 'Gets the file number associated with the given name'
BEGIN
    DECLARE v_id BIGINT;

    SELECT id INTO v_id FROM Filename WHERE name = p_name;

    RETURN v_id;
END;
//

DROP PROCEDURE IF EXISTS TestCall//
CREATE PROCEDURE TestCall(p_name VARCHAR(255))
COMMENT 'Tests function call within a procedure'
BEGIN
    SELECT 'Returns: ' || COALESCE(GenerateFileNumber(p_name), 'none') AS msg;
END;
//

DELIMITER ;

Here's how it works:

mysql> CALL TestCall('CCC');
+------------+
| msg        |
+------------+
| Returns: 3 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL TestCall('XXX');
+---------------+
| msg           |
+---------------+
| Returns: none |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Answer 2

This will work when you want to return multiple values.

DROP TABLE IF EXISTS Filename;
CREATE TABLE Filename(
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO Filename(name) VALUES ('AAA'), ('BBB'), ('CCC'), ('DDD'), ('EEE');

DELIMITER //
DROP PROCEDURE IF EXISTS GenerateFileNumber//
CREATE PROCEDURE GenerateFileNumber(p_name VARCHAR(255))
COMMENT 'Gets the file number associated with the given name'
BEGIN
    DECLARE v_id BIGINT;

    DROP TABLE IF EXISTS ReturnValues;
    CREATE TEMPORARY TABLE ReturnValues (
        val1 BIGINT(20) UNSIGNED,
        str1 VARCHAR(255)
    );

    SELECT id INTO v_id FROM Filename WHERE name = p_name;
    INSERT INTO ReturnValues(val1, str1) VALUES (v_id, 'Moop');
END;
//

DROP PROCEDURE IF EXISTS TestCall//
CREATE PROCEDURE TestCall(p_name VARCHAR(255))
COMMENT 'Tests function call within a procedure'
BEGIN
    DECLARE v_id BIGINT;
    DECLARE v_str VARCHAR(255);

    CALL GenerateFileNumber(p_name);

    SELECT val1, str1 INTO v_id, v_str FROM ReturnValues;

    SELECT 'Returns: ' || COALESCE(v_id, 'none') || ', ' || v_str AS msg;
END;
//

DELIMITER ;

In general, though, the previous function-based answer should be used when possible. It runs faster, and you don't need to worry about accidentally clobbering your temp table.

mysql> call TestCall('AAA');
+------------------+
| msg              |
+------------------+
| Returns: 1, Moop |
+------------------+
1 row in set (0.27 sec)

Query OK, 0 rows affected (0.27 sec)

mysql> call TestCall('CCC');
+------------------+
| msg              |
+------------------+
| Returns: 3, Moop |
+------------------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.17 sec)