Search code examples
mysqlstored-proceduresnulloutputprocedure

Why is mySQL procedure returning null when called from within procedure, but not when called on it's own


I have a mySQL procedure that is calling another procedure to build a concatenated TEXT field. My insert statement is giving an error of "script can not be null" when the outer procedure is called. When I call the inner procedure on it's own, it is returning the proper value. What gives? it is probably something simple, but I'm not seeing it.

OUTER PROCEDURE

DELIMITER $$

USE `automation`$$

DROP PROCEDURE IF EXISTS `pd_build_script`$$

CREATE DEFINER=`user`@`%` PROCEDURE `pd_build_script`(
    IN autom_type INT(3),
    IN auto_font VARCHAR(30),
    IN auto_design VARCHAR(10),
    IN auto_d_only TINYINT(1),
    IN auto_job VARCHAR(255),
    IN auto_template VARCHAR(75)
)
BEGIN
    DECLARE font VARCHAR(30);
    DECLARE design_script TEXT;

    SELECT indesign_name INTO font FROM automation.`indesign_font` WHERE auto_type = autom_type AND web_name = auto_font;
    IF (auto_design = "PD00") THEN
        CALL pd00(font, @out_script);
    ELSEIF (auto_design = "PD1" OR auto_design = "PD73" OR auto_design = "PD88" 
        OR auto_design = "PD97") THEN 
        CALL single_letter_art(font, auto_design, auto_d_only, @out_script);
    ELSEIF (auto_design = "PD2") THEN
        CALL pd2(font, auto_d_only, @out_script);
    ELSEIF (auto_design = "PD3") THEN
        CALL pd3(font, auto_d_only, @out_script);
    ELSEIF (auto_design = "PD28") THEN
        CALL pd28(font, auto_d_only, @out_script);
    ELSE
        CALL pd_general(font, auto_design, auto_d_only, @out_script);
    END IF;   

    SET design_script = CONCAT("some text", @out_script, "more text");
    INSERT INTO automation.`job_script` (job_id, script, batch) 
    VALUE (auto_job, @out_script, CONCAT('batch text', auto_job));
END$$

DELIMITER ;

INNER PROCEDURE

DELIMITER $$

USE `automation`$$

DROP PROCEDURE IF EXISTS `pd2`$$

CREATE DEFINER=`user`@`%` PROCEDURE `pd2`(
    IN font VARCHAR(30), design_only TINYINT(1), OUT layers TEXT
)
BEGIN
        DECLARE layer TEXT;
        DECLARE links TEXT;
        IF (design_only = 1) THEN
            SET layer = "layer text";
        ELSE
            SET layer = CONCAT("layer text",font,"more layer text");        
        END IF;
        #Set the links for the design.
        SET links = "links";
        SELECT CONCAT(layer,links) INTO layers;
    END$$

DELIMITER ;

Solution

  • Found out what the problem was. The select for the font variable was returning null, as the option being passed was not in the DB. This caused a cascade of null data. Issue wasn't actually in the code.