Re: Can I use a dynamic table name in stored procedure --> SOLVED

[Michael Gargiullo]

Beautiful!

Thank you Martin.

For those who would like an example of the full working sp:


DELIMITER |
create procedure test (IN Rule INT, IN RuleTable varchar(25), IN min INT)
BEGIN
DECLARE num_v INT;
SET @num_v=-1;
SET @dyn_sql=CONCAT('Select count(*) into @num_v from ',RuleTable,' where TimeStamp > (now() - INTERVAL ? MINUTE)');
PREPARE s1 from @dyn_sql;
SET @min_val=min;
EXECUTE s1 USING @min_val;
DEALLOCATE PREPARE s1;
IF( @num_v < 1) THEN
SET @dyn_ins="insert into EventEngineAlerts values ('',?,now(),0)";
PREPARE s2 from @dyn_ins;
SET @rule_val=Rule;
EXECUTE s2 USING @rule_val;
DEALLOCATE PREPARE s2;
END IF;
END;
|
DELIMITER ;



Edited 1 time(s). Last edit at 11/14/2006 08:57PM by Michael Gargiullo.