Can any one post me Stored Procedure for Mysql5.0 in Linux environment. I have create Stored Procedure but it show Exception as Syntax error check for Version.
My need is i want to take sum value from on table and convert it into String and Inserted into another table with date and time.
This is my procedure; Can any one correct it for me;
delimiter //
DROP PROCEDURE IF EXISTS sampleTable.sampleTableProcedure//
CREATE DEFINER = 'root'@'localhost' PROCEDURE sampleTable.sampleTableProcedure(OUT currentDate VARCHAR(50), OUT currentTime VARCHAR(50), OUT summationVarChar VARCHAR(50))
BEGIN
DECLARE summation DOUBLE;
DECLARE noOfRows INT;
DECLARE sampleRecordSize VARCHAR(50);
SELECT SUM(FileSize) INTO summation FROM sampleFileTable;
IF summation > 1024 AND summation < 1048576 THEN
SELECT summation/1024 INTO summation;
SET summationVarChar = CONCAT(summation, ' KB');
ELSEIF summation > 1048576 AND summation < 1073741824 THEN
SELECT summation/1048576 INTO summation;
SET summationVarChar = CONCAT(summation, ' MB');
ELSEIF summation > 1073741824 THEN
SELECT summation/1073741824 INTO summation;
SET summationVarChar = CONCAT(summation, ' GB');
ELSE
SET summationVarChar = CONCAT('0', ' KB');
END IF;
SELECT COUNT(*) INTO noOfRows FROM sampleRecord;
SELECT Size INTO sampleRecordSize FROM sampleRecord ORDER BY PrimaryKey DESC LIMIT 1; // taking last entry in table
SET currentDate = CURDATE();
SET currentTime = CURTIME();
IF sampleRecordSize<>summationVarChar OR noOfRows=0 THEN
INSERT INTO sampleRecord(CurrentDate,CurrentTime,Size) VALUES(currentDate, currentTime, summationVarChar);
END IF;
END;
//
delimiter ;
Its work Perfectly in HeidiSql 4.0 in windows environment