How do i make this Stored Procedure worl well?
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>