Search code examples
mysqlsqlif-statementstored-procedurescursor

SQL Syntax error #1064 cursors (mysql)?


So my procedure looks something like this:

OPEN tstamp_cursor;

get_timestamp: LOOP 

FETCH tstamp_cursor INTO cSID, ctstamp; 

IF finished = 1 THEN 
LEAVE get_timestamp ;
END IF;

SET @MID = 0;
SET @MX = 0;
SET @MY = 0;
SET @MT = 0;

  SELECT @MID:=m.vID, @MX:=m.x , @MY:=m.y , @MT:=m.timestamp FROM movement m
  WHERE m.vID = cSID 
  AND m.timestamp = (SELECT MAX(T.timestamp) 
                     FROM (SELECT mm.timestamp FROM movement mm WHERE mm.vID = cSID AND mm.timestamp <= ctstamp)AS T);

IF @MT = '0000-00-00 00:00:00' THEN  
    SELECT @MID:=m.vID, @MX:=m.x , @MY:=m.y , @MT:=m.timestamp FROM movement m 
    WHERE m.vID = cSID
    AND m.timestamp = (SELECT MIN(T.timestamp) 
                     FROM (SELECT mm.timestamp FROM movement mm WHERE mm.vID = cSID AND mm.timestamp >= ctstamp)AS T);



INSERT INTO communicationLocation(`ctimestamp`,`vID`,`x`,`y`,`mtimestamp`) #to insert into every row 
VALUES(ctstamp,@MID,@MX,@MY,@MT); 



END LOOP get_timestamp; 

close tstamp_cursor;

END

;        

where cSID and ctstamp are cursor variables. and I get this error:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near 
'LOOP get_timestamp; 

close tstamp_cursor;    

END' at line 50      

I don't understand whats wrong... is it the IF statement? Because I mean, without the IF statement, I don't get an error... but I don't really know whats wrong with the IF statement either.

Any help would be greatly appreciated, thanks!!

EDIT

Do I need to make this into a SELECT...IF something like this question?


Solution

  • The END IF of the second IF is missing.