MySQL Forums
Forum List  »  Stored Procedures

How do i make this Stored Procedure worl well?
Posted by: Daniel Feliciano
Date: April 11, 2013 01:53PM

OK so this is my code, Basically It lats too long when I call it and put on the parameters for example,10 min, I called it like this

call search('2013-02-17','2013-02-17 00:10:00','%port%');

again query works, but it lasts too long.. How do I make it work the appropriate time,, so when I call it ,, it stops when 10 min pass on the query?
*************************************
DROP PROCEDURE IF EXISTS search;
DELIMITER ;;
create procedure search
(
IN startTime datetime,
IN endTime datetime,
IN searchString varchar(255)
)
BEGIN
DECLARE t int;
SET t = 0;
SET @outvar = 0;
SET @outvar2 = @outvar;
WHILE startTime<=endTime DO
SET @query = CONCAT('SELECT tableName FROM syslogindex WHERE startDate =\'',startTime,'\' INTO @outvar ;');
PREPARE STMT FROM @query;
EXECUTE STMT;
SET @query3 = CONCAT('CREATE TABLE IF NOT EXISTS `',@outvar,'` (ID int(10) unsigned, CustomerID bigint(20), ReceivedAt datetime, DeviceReportedTime datetime,
Facility smallint(6), Priority smallint(6), FromHost varchar(60), Message text, NTSeverity int(11),
Importance int(11), EventSource varchar(60), EventUser varchar(60), EventCategory int(11), EventID int(11), EventBinaryData text, MaxAvailable int(11), CurrUsage int(11),
MinUsage int(11), MaxUsage int(11), InfoUnitID int(11), SysLogTag varchar(60),
EventLogType varchar(60), GenericFileName varchar(60), SystemID int(11), processid varchar(60), checksum int(11))');
PREPARE STMT FROM @query3;
EXECUTE STMT;
IF @outvar2 != @outvar THEN
IF t > 0 THEN
SET @query2 = CONCAT(@query2,' union (select * from `',@outvar, '` WHERE Message LIKE \'',searchString,'\')');
ELSEIF t < 1 THEN
SET @query2 = CONCAT('(select * from `',@outvar, '` WHERE Message LIKE \'',searchString,'\')');
SET t = 1;
END IF;
END IF;
SET @outvar2 = @outvar;
SET startTime = DATE_ADD(startTime,INTERVAL 1 DAY);
END WHILE;
SET @query2 = CONCAT(@query2,';');
PREPARE STMT FROM @query2;
EXECUTE STMT;
END;;
DELIMITER ;



Hope you can help me,,would really apreciate it,, thanks!
Daniel

P.S,, I input each statement part by part, and when I did it, it marked and error on
*********************************
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHILE
;
SET @query2 = CONCAT(@query2,';');
PREPARE STMT FROM @query2;
EXECUTE STM' at line 28
mysql>

Options: ReplyQuote


Subject
Views
Written By
Posted
How do i make this Stored Procedure worl well?
1847
April 11, 2013 01:53PM


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.