Syntax Error on Sproc
Hello there
I have a syntax error on the code below.
************************************
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 ;
*******************************
Basically the function works, but it stays too long retrieving records, for example I put this:
call search('2013-02-17','2013-02-17 00:00:10','%port%'); <this are the parameters>
and it takes around 20 min to finish
I input the code part by part and it says there is a syntax error in this specific part:
***************
WHILE;
SET @query2= CONCAT(@query2,';');
PREPARE STMT FROM @query2;
EXECUTE STMT;
***************
How can I solve this? What should I put in order to get the proper value of time when I call the stored procedure?
Is it a problem involving the "query2" query?
Thank you in advance
Edited 1 time(s). Last edit at 03/13/2013 09:45AM by Daniel Feliciano.