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