Skip navigation links

MySQL Forums :: Stored Procedures :: Can I use a dynamic table name in stored procedure


Advanced Search

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
Can I use a dynamic table name in stored procedure 15839 Michael Gargiullo 11/14/2006 10:48AM
Re: Can I use a dynamic table name in stored procedure 6023 Martin Leboeuf 11/14/2006 11:08AM
Re: Can I use a dynamic table name in stored procedure 5413 Michael Gargiullo 11/14/2006 12:11PM
Re: Can I use a dynamic table name in stored procedure 5050 Martin Leboeuf 11/14/2006 02:16PM
Re: Can I use a dynamic table name in stored procedure --> SOLVED 15650 Michael Gargiullo 11/14/2006 08:56PM
Re: Can I use a dynamic table name in stored procedure --> SOLVED 5200 ganesh venugopal 04/24/2008 12:57AM
Re: Can I use a dynamic table name in stored procedure --> SOLVED 3977 Mike Zielinski 04/28/2008 04:40AM


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.