Saturday, 24 August 2013

minimizing parameters in MySQL stored procedure

minimizing parameters in MySQL stored procedure

I've got a stored procedure that selects IDvars based on 'foo''s value and
assigns them variable names that include foo. I can do this with:
CALL db0.genericStoredProc(IDvar, "foo", "'foo'");
Ideally, I'd just like to write
CALL db0.genericStoredProc(IDvar, "foo");
How do I leverage bar, such that I don't need a bar2 parameter? Here are
the relevant parts of my stored procedure.
DROP PROCEDURE IF EXISTS genericStoredProc;
DELIMITER $ $
CREATE PROCEDURE genericStoredProc(myID BIGINT(20) UNSIGNED, bar
VARCHAR(5), bar2 VARCHAR(5))
BEGIN
SET @s = CONCAT('SELECT gt.name, gt.junk, gt.junkName, count(gt.idvar) as
id_count,
SUM(CASE
WHEN ot.offset <0 THEN 1
ELSE 0
END) AS ', bar,'_id_count
FROM genericTable gt
LEFT JOIN otherTable ot ON gt.IDvar2 = ot.IDvar
WHERE (gt.idvar = ', IDvar, ') AND (ot.prmtr = ', bar2, ')
GROUP BY gt.idvar');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

No comments:

Post a Comment