How to convert a SQL Server cursor to MySQL equivalent
How can I convert the below procedure to MySQL format?
Here is the snippet 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 if there is something wrong.
+2
source to share
1 answer
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 a loop using CONTINUE HANDLER to set the flag when there are no more lines to fetch and exit the loop when the flag is set. (This looks ugly, but the way it is done in MySQL.)
This example begs the question why it is not written (more efficiently in both SQL Server and MySQL) like this:
INSERT INTO ElderCustomers (FirstName, Age)
SELECT FirstName, Age
FROM Customers
WHERE Age > 60
+3
source to share