0

I would like to use a transaction to call upon a stored procedure on many rows. If it fails on a single row, I want to roll back everything. Otherwise I want to commit.

I have the following so far but it throws the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE val1 INT DEFAULT 0; DECLARE check INT DEFAULT 1; DECLARE done BOOL DEFAU' at line 3

 DELIMITER \\
 BEGIN

 DECLARE val1 INT DEFAULT 0;
 DECLARE check INT DEFAULT 1; 
 DECLARE done BOOL DEFAULT FALSE;
 DECLARE e INT DEFAULT 1;
 DECLARE cursor1 CURSOR FOR SELECT Table1.col1 FROM Table1 WHERE Table.col2 = (SELECT col1 FROM Table2 WHERE col3 = 'xxx');

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 START TRANSACTION;

 OPEN cursor1;

 my_loop:
 LOOP

    FETCH NEXT FROM cursor1 INTO val1;
    IF done THEN
            LEAVE my_loop;
    ELSE
            storedProcedure1(val1,100,e);
            IF SELECT e = 0 THEN
                    ROLLBACK;
                    SET check = 0;
                    LEAVE my_loop;
            END IF;
    END IF;
  END LOOP;

  IF check = 1 THEN
       COMMIT;
  END IF;
  END;

Any ideas on what I may be doing wrong?

2
  • 1
    Missing the END? Not setting the delimiter to something other than the semicolon?
    – mustaccio
    Mar 14, 2018 at 15:44
  • I have those; just forgot to add them here. Have edited my question.
    – MSJ
    Mar 14, 2018 at 15:58

2 Answers 2

2

If you want to create a stored procedure, you have to use the CREATE PROCEDURE syntax, see the documentation. You can't use procedural language constructs such as DECLARE in normal SQL.

Also, you have to use the special delimiter you've declared after the final END, so the last line will be END\\. (And after that, don't forget to set DELIMITER back to ;, or you'll struggle.)

(There could be other problems as well, these are just two I spotted right away.)

0

You missed the CREATE PROCEDURE PART, doing a quick refactor the script should be written like this...

DELIMITER \\
CREATE PROCEDURE your_sp_name()
BEGIN

DECLARE val1 INT DEFAULT 0;
DECLARE check INT DEFAULT 1; 
DECLARE done BOOL DEFAULT FALSE;
DECLARE e INT DEFAULT 1;
DECLARE cursor1 CURSOR FOR SELECT Table1.col1 FROM Table1 WHERE table_name.col2 IN (SELECT col1 FROM Table2 WHERE col3 = 'xxx');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

START TRANSACTION;

OPEN cursor1;

my_loop:
LOOP

FETCH cursor1 INTO val1;
IF done THEN
   LEAVE my_loop;
ELSE
   CALL storedProcedure1(val1,100,e);
   IF e = 0 THEN
        ROLLBACK;
        SET check = 0;
        LEAVE my_loop;
   END IF;
END IF;
END LOOP;

IF check = 1 THEN
    COMMIT;
END IF;

CLOSE cursor1;
END;

DELIMITER ;

Not the answer you're looking for? Browse other questions tagged or ask your own question.