tags:

views:

10

answers:

0

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