0

NOTE: I have already referenced the question on Call a stored procedure for each row returned by a query in MySQL and have modeled my code after it.

I had created a stored procedure called NearbyCities and it works when I call it by inputting one location. Now, I have 660 locations and rather than calling it 660 times manually, I discovered that I could create another procedure using cursors to do this job for me. So by referencing various resources, I had came out with the following code:

DELIMITER //
    CREATE PROCEDURE LocationCursor()
        BEGIN
            DECLARE `Finished` INT DEFAULT FALSE;
            DECLARE `ID` VARCHAR(5);
            DECLARE `Location` VARCHAR(255);
            DECLARE `Street` VARCHAR(255);
            DECLARE `City` VARCHAR(255);
            DECLARE `State` VARCHAR(255);
            DECLARE `Zip Code` VARCHAR(255);
            DECLARE `Latitude` VARCHAR(255);
            DECLARE `Longitude` VARCHAR(255);

            DECLARE `LocCursor` CURSOR
                FOR SELECT `ID`
                          ,`Location`
                          ,`Street`
                          ,`City`
                          ,`State`
                          ,`Zip Code`
                          ,`Latitude`
                          ,`Longitude`
                    FROM `LocationDirectory`;

            DECLARE CONTINUE HANDLER FOR NOT FOUND SET `Finished` = TRUE;

            OPEN `LocCursor`;

            ReadLoop: LOOP
                FETCH NEXT FROM `LocCursor` INTO `ID`
                          ,`Location`
                          ,`Street`
                          ,`City`
                          ,`State`
                          ,`Zip Code`
                          ,`Latitude`
                          ,`Longitude`;

                IF `Finished` = TRUE
                    THEN LEAVE ReadLoop;
                END IF;

                CALL NearbyCities(`ID`);

            END LOOP ReadLoop;

            CLOSE `LocCursor`;

        END //
DELIMITER ;

CALL LocationCursor();

Now, the code compiles, but the results table shows nothing when I call it. What am I missing?

Cinji18
  • 619
  • 8
  • 22
  • 1
    Don't use variables that have the same names as the columns in the query. – Barmar Jan 27 '18 at 00:14
  • Does it work if you change all the variables so they don't conflict? – Barmar Jan 27 '18 at 00:15
  • I'm not sure I understand. Are you saying that the variable names in my cursor SELECT statement need to be different from the variable names in my fetch loop? – Cinji18 Jan 27 '18 at 00:28
  • I'm saying the variable names that you `DECLARE` at the top should not have the same names as the columns in the table that you `SELECT` from. – Barmar Jan 27 '18 at 00:30
  • Oohhh. Let me try that. – Cinji18 Jan 27 '18 at 00:35
  • Well, the procedure compiled, but when I call it, it says there's an unknown column in the field list. Not sure why it's unknown because I have been using this column for a while. I need to look into this. – Cinji18 Jan 27 '18 at 00:41

1 Answers1

0

The problem is that you have procedure variables with the same names as your table columns. In the scope of the procedure, those names refer to the variables, not the columns. So when you write:

SELECT ID, Location, ...

it's returning the values of the variables, not the table columns.

There are two ways to resolve this:

  1. Use names that don't conflict with table columns, e.g.

    DECLARE v_ID VARCHAR(5);
DECLARE v_Location VARCHAR(255);
...

  2. Refer to the table columns with an explicit table prefix:

    SELECT LocationDirectory.ID, LocationDirectory.Location, ...

Assigning a table alias can be helpful in the latter case.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Well, I already tried the first one, and that came out with an undeclared variable error. I will try the second option. – Cinji18 Jan 27 '18 at 01:03
  • You should still get an unknown column error with the second method. You must have one of the column names wrong, and the variable name was hiding the error. – Barmar Jan 27 '18 at 01:05
  • Got it!! Thanks. – Cinji18 Jan 27 '18 at 23:38
  • So this procedure compiled, but when I call it (CALL LocationCursor()), it only returns the column names. Nothing else. – Cinji18 Jan 27 '18 at 23:56
  • Maybe there's a problem in the `NearbyCities()` function, since that's the one that returns the results. – Barmar Jan 28 '18 at 03:12