Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - Error #1193 - Unknown system variable 'FETCH_STATUS' [duplicate]

How can I convert the procedure below to MySQL format?

Here is the piece to be converted:

DECLARE @CurrentFirstName varchar(300)
DECLARE @CurrentAge INT

DECLARE CursorName CURSOR FAST_FORWARD FOR 
    SELECT Firstname,Age 
    FROM Customers

OPEN CursorName
FETCH NEXT FROM CursorName INTO @CurrentFirstName, @CurrentAge

WHILE @@FETCH_STATUS = 0
BEGIN
      IF @AGE>60 /*this is stupid but we can apply any complex condition here*/ BEGIN
    insert into ElderCustomers values (@CurrentFirstName,@CurrentAge)
      END


    FETCH NEXT FROM CursorName INTO @CurrentFirstname,@CurrentAge
END

CLOSE CursorName
DEALLOCATE CursorName

Sorry in advance if there is something wrong above

like image 279
Junior Mayhé Avatar asked Nov 22 '22 11:11

Junior Mayhé


1 Answers

The MySQL equivalent would be something like this:

BEGIN
  DECLARE CurrentFirstName VARCHAR(300);
  DECLARE CurrentAge INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE CursorName CURSOR FOR
    SELECT FirstName, Age FROM Customers;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN CursorName;
  myloop: LOOP
    FETCH CursorName INTO CurrentFirstName, CurrentAge;
    IF done THEN
      LEAVE myloop;
    END IF;
    IF CurrentAge > 60 THEN
      insert into ElderCustomers values (CurrentFirstName,CurrentAge);
    END IF;
  END LOOP;
  CLOSE CursorName;
END;

The big difference is in the loop, using the CONTINUE HANDLER to set a flag when there are no more rows to fetch, and exiting the loop when the flag is set. (That looks ugly, but that's the way it's done in MySQL.)

This example begs the question why this isn't written (more efficiently, in both SQL Server and MySQL) as:

INSERT INTO ElderCustomers (FirstName, Age)
SELECT FirstName, Age
  FROM Customers
 WHERE Age > 60
like image 70
spencer7593 Avatar answered Dec 10 '22 04:12

spencer7593