4

I am trying to use a cursor in MySQL to call a stored procedure many times. I want to call it as many times as a value for my_id exists in some temporary table, and iterate through those ids and concatenate the results.

Anyway, I'm having trouble with this part of the process:

DECLARE curs CURSOR FOR  
  SELECT something FROM somewhere;

I don't want to select something from somewhere. I want something like

DECLARE curs CURSOR FOR  
  CALL storedproc(@an_id);

Can the DECLARE statement be used to call a stored procedure? Or does it have to be associated with a SELECT only? Googling around, I'm afraid that the latter is the case.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Monica Heddneck
  • 2,973
  • 10
  • 55
  • 89
  • Beware the [XY](http://xyproblem.info). The reason this doesn't work is the same reason you can't `CALL my_procedure() WHERE ... ORDER BY ... LIMIT ...`, etc. However, it sort of sounds like what you are looking for is a stored *function*, which returns a scalar value and can be used repeatedly in a cursor with values from the table from which the cursor will select rows. – Michael - sqlbot Mar 09 '16 at 00:50
  • You are totally right. Actually, this is a followup question. I did attempt to create a discussion about my issue directly -- it got marked as a duplicate and pointed me towards exploring cursors. For better or worse... http://stackoverflow.com/questions/34403106/iterate-through-a-column-to-give-input-to-a-stored-procedure-in-mysql – Monica Heddneck Mar 10 '16 at 06:12
  • Now, now. :) There's nothing wrong with cursors. I'll see if I can get you pointed in the right direction if you promise to quit hating on MySQL. – Michael - sqlbot Mar 10 '16 at 11:00
  • I'm starting to hate it!! My SO questions read like an indictment... – Monica Heddneck Mar 10 '16 at 18:38
  • I've written an example of the standard supporting code that you'd need with a cursor, specifically when calling another proc. My contact info can be found on [my SO profile](http://stackoverflow.com/users/1695906/michael-sqlbot?tab=profile) if you need further assistance in your conversion to becoming a MySQL afficionado. – Michael - sqlbot Mar 10 '16 at 21:31

2 Answers2

14

Using a cursor requires some standard boilerplate code to surround it.

Using a cursor to call a stored procedure for each set of values from the table requires essentially the same boilerplate. You SELECT the values you want to pass, from wherever you're getting them (which could be a temporary table, base table, or view, and can include calls to stored functions) and then call the procedure with those values.

I've written an syntactically valid example of that boilerplate code, below, with comments to explain what each component is doing. There are few things I dislike more than being asked to just do something "just because" -- so everything is (hopefully) explained.

You mentioned calling the procedure with multiple values, so this example uses 2.

Note that there events that happen her are in a specific order for a reason. Variables have to be declared first, cursors have to be declared before their continue handlers, and loops have to follow all of those things. This gives an impression that there's some fairly extreme inflexibility, here, but that's not really the case. You can reset the ordering by nesting additional code inside BEGIN ... END blocks within the procedure body; for example, if you needed a second cursor inside the loop, you'd just declare it inside the loop, inside another BEGIN ... END.

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might not need one
BEGIN

-- from http://stackoverflow.com/questions/35858541/call-a-stored-procedure-from-the-declare-statement-when-using-cursors-in-mysql

-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types.

DECLARE val1 INT DEFAULT NULL;
DECLARE val2 INT DEFAULT NULL;

-- we need a boolean variable to tell us when the cursor is out of data

DECLARE done TINYINT DEFAULT FALSE;

-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection

DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
 CURSOR FOR
 SELECT t1.c1, 
        t1.c2
   FROM table1 t1
  WHERE c3 = arg1; 

-- this fancy spacing is of course not required; all of this could go on the same line.

-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.   

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- open the cursor

OPEN cursor1;

my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP

  -- read the values from the next row that is available in the cursor

  FETCH NEXT FROM cursor1 INTO val1, val2;

  IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
    LEAVE my_loop; 
  ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1, 
       -- so now we call the procedure with them for this "row"
    CALL the_other_procedure(val1,val2);
    -- maybe do more stuff here
  END IF;
END LOOP;

-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here

END $$

DELIMITER ;
Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • This is amazing and fantastic. I can tell you love MySQL and you want others to as well. So, as I've begun to digest this, first off I noticed a syntax error at `FETCH NEXT FROM cursor1 INTO val1;` ? – Monica Heddneck Mar 16 '16 at 23:09
  • Before posting this, I tested the proc for syntax on whatever version was handy at the time I wrote it. I'll verify it again. What version are you running (`SELECT @@VERSION;`) and what's the exact error? ("...for the correct syntax to use near 'FETCH..." or something else?) – Michael - sqlbot Mar 16 '16 at 23:29
  • Note that MySQL syntax errors tend to tell you about what was unexpected (think in terms of left-to-right parsing), meaning that the thing mentioned in the error often should have been preceded by something that is missing... or the thing in the error was found where something different was expected -- meaning the actual error is at, or right before, the part actually quoted in the message. – Michael - sqlbot Mar 16 '16 at 23:31
  • Confirmed, valid syntax on MySQL Server 5.1, 5.5, 5.6. See also the [SQL Fiddle](http://sqlfiddle.com/#!9/39a0f5/2) with the same code. Note that Fiddle handles `DELIMITER` differently, requiring you to select a delimiter from a drop-down list, rather than using the `DELIMITER` client statement, and only supports `//` rather than my standard choice of `$$` ,,, but otherwise the fiddle is a copy-paste of the answer. – Michael - sqlbot Mar 17 '16 at 14:11
  • This is really weird. I'm on `5.6.28-0ubuntu0.14.04.1-log` and the error message says `Syntax error: FROM is not valid inputs at this position` http://postimg.org/image/i76jk3akd/ I wonder if this is a MySQL Workbench problem? PS I really appreciate your advice. – Monica Heddneck Mar 18 '16 at 03:03
  • Remember how you were hating on MySQL Server? Workbench actually *deserves* that sentiment. It's utterly dreadful, for reasons like this one. I don't use it. That message is not from the server. The valid syntax is actually `FETCH [[NEXT] FROM] cursor_name` -- I use the full statement because it's more expressive, but you can eliminate `NEXT` or `NEXT FROM` and the meaning is the same. Perhaps that will pacify it. https://dev.mysql.com/doc/refman/5.6/en/fetch.html. You might also check for Workbench upgrades. – Michael - sqlbot Mar 18 '16 at 05:01
  • @Michael-sqlbot Is there a preferable MySQL IDE (or RDBMS, or whichever term you prefer) other than MySQL Workbench? – StockB Jan 27 '17 at 19:02
  • The Query Browser found in the [GUI Tools package](https://downloads.mysql.com/archives/gui/) is what I use all day, every day, as a DBA. This was discontinued years ago but it still does exactly what it needs to do, and not much more, in contrast to the bloatware that is Workbench. – Michael - sqlbot Jan 27 '17 at 21:01
  • this is a great example, but in my case the loop would exit after running exactly once. i was calling a couple of stored procedures, and in one of them i guess the exception flag got set and "bubbled up" for lack of a better term. i ended up replacing the exception with a SELECT COUNT(*) and using a counter instead. – niko Aug 08 '23 at 01:43
0

Can the DECLARE statement be used to call a stored proc?

Not possible and documentation is pretty clear on that

Cursor DECLARE Syntax
This statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor. To fetch the rows later, use a FETCH statement. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • This is depressing. This means there is no solution for me. I officially hate MySQL! – Monica Heddneck Mar 08 '16 at 04:12
  • Open a cursor to read your ids from the temp table and call your procedure while you're iterating over this cursor. – peterm Mar 08 '16 at 04:21
  • But I can't call the procedure while I'm iterating over the cursor -- I can only call a SELECT because of the limitation in the DECLARE statement, as mentioned in the answer by @peterm – Monica Heddneck Mar 08 '16 at 05:32