MySQL Forums
Forum List  »  Stored Procedures

Re: Can I use a dynamic table name in stored procedure --> SOLVED
Posted by: Michael Gargiullo
Date: November 14, 2006 08:56PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can I use a dynamic table name in stored procedure --> SOLVED
22098
November 14, 2006 08:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.