MySQL Stored Procedure LIKE syntaxt in Cursor Declaration

Question

I am very green when it comes to MySQL Stored Procedures and i have been having trouble with selecting all payments received in the current month and looping through them to see which accounts did not pay the monthly fee, my problem seems to be with the LIKE statement in the declaration of the cursor. Here is the code:

CREATE DEFINER=`wisper`@`%` PROCEDURE `process_rejections`()
BEGIN
    DECLARE cursor_ID INT(11);
    DECLARE account_id INT(11);
    DECLARE amount_paid DECIMAL(10,2);
    DECLARE date_paid DATETIME;
    DECLARE cur_year INT DEFAULT (SELECT YEAR(CURRENT_DATE()));
    DECLARE cur_month INT DEFAULT (SELECT MONTH(CURRENT_DATE()));
    DECLARE comp_date VARCHAR(10) DEFAULT (SELECT CONCAT(cur_year,'-',cur_month));
    DECLARE done INT DEFAULT FALSE;

    DECLARE cursor_i CURSOR FOR 
    SELECT `id`, `account_id`, `amount_paid`, `date` 
    FROM `payments_received` 
    WHERE `date`LIKE CONCAT('%',@comp_date,'%');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cursor_i;
    read_loop: LOOP
        FETCH cursor_i INTO cursor_ID, account_id, amount_paid, date_paid;
            IF done THEN
                LEAVE read_loop;
            END IF;
            //Do Stuff here like change the status of the account
        END LOOP;
    CLOSE cursor_i;
END

IF i remove this:

WHERE `date` LIKE CONCAT('%',@comp_date,'%');

Everything works but it obviously selects * payments received instead of those that occured during ,for example, Aug 2017. I do not want to select * because over time there will be hundreds of thousands of rows in the specific table and i do not want the overhead to become too much. I have also tested this:

WHERE `date` LIKE '2017-08-11';

Which also does not work. During debuggin i also tried to use a specifi date like this:

WHERE `date` = '2017-08-11';

And everything worked nicely but it is obviously just to test and see if the WHERE cluase was syntactically correct inside the cursor declaration.

I am at a lost here and would appreciate some assistance from you guy's.

Thank You in advance.

Life lesson: Avoid the sentence "does not work" when posting questions. If it would, you wouldn't be asking. Always post error messages instead or describe what is happening. There is no useful information in "does not work". — fancyPants, Aug 16 '17 at 08:07
There isnt any errrors, it returns zero rows, which should not be the case. — J.L Nel, Aug 16 '17 at 08:11
The problem is the use of an undefined session variable... see my answer below. — wally, Aug 16 '17 at 08:19

wally · Answer 1 · 2017-08-16T08:17:52.930

1

You've debugged your code well, and you've isolated the problem successfully. It's not the LIKE itself, or the fact it is a stored procedure that's tripping you up.

Problem: You're using a session variable (the variable is prepended with an @), not the stored procedure variable declared a few lines earlier.

Solution: Drop the @ (on comp_date):

WHERE `date` LIKE CONCAT('%', comp_date, '%');

Assuming the session variable isn't set, I believe you'll be getting NULL, which won't work. Thus the symptom you're seeing.

There's some useful explanation on another SO question here: MySQL: @variable vs. variable. Whats the difference?

edited Aug 16 '17 at 08:17

answered Aug 16 '17 at 08:06

wally

3,492
25
31

Thanks for your responce. – J.L Nel Aug 16 '17 at 08:48
I have dropped the '@' but is still return zero rows. i think my problem lies with missing quotes in the LIKE, for instance '%2017-08%' works but i am struggling to concat them into the declaration. I have tried CONCAT("'%",comp_date,"%'") but it still return zero rows. – J.L Nel Aug 16 '17 at 08:51
Can you try adding a simple `SELECT CONCAT('%',@comp_date,'%')` in the stored procedure to debug what value it returns? (You should get a second result set, which will be visible in your client. Failing that, do a `SELECT CONCAT('%',@comp_date,'%') INTO @debug` and then call `SELECT @debug;` after the stored proc runs.) – wally Aug 16 '17 at 09:10
I'm comfortable the mechanism you're using (as originally described) should work given my own tests: `> SELECT created_at FROM customers WHERE created_at LIKE CONCAT('%2017-08%');` returns `| 2017-08-09 21:48:41 |` – wally Aug 16 '17 at 09:11

score 0 · Answer 2 · answered Aug 16 '17 at 09:36

I have found the problem, turns out the comp_date looks like this 2017-8 instead of using leading zeros like this 2017-08, here is the finished code:

PS: Yes i feel like an idiot for not testing the values that i submit in the statement, but maybe someone else also struggles with it and this can help them.

CREATE DEFINER=`wesley`@`%` PROCEDURE `process_rejections`()
BEGIN
    DECLARE cursor_ID INT(11);
    DECLARE account_id INT(11);
    DECLARE amount_paid DECIMAL(10,2);
    DECLARE date_paid DATETIME;

    DECLARE comp_date VARCHAR(10) DEFAULT 
    CONCAT(YEAR(CURRENT_DATE()), '-', LPAD(MONTH(CURRENT_DATE()), 2, '0'));

    DECLARE done INT DEFAULT FALSE;
    DECLARE cursor_i CURSOR 
    FOR SELECT `id`, `account_id`, `amount_paid`, `date` 
    FROM `payments_received` 
    WHERE `date` 
    LIKE CONCAT('%', comp_date,'%');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursor_i;
    read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, account_id, amount_paid, date_paid;
            IF done THEN
                LEAVE read_loop;
            END IF;
        //Do stuff here like changing account status
        END LOOP;
    CLOSE cursor_i;
END

Note how i declare comp_date to account for leading zeros.

MySQL Stored Procedure LIKE syntaxt in Cursor Declaration

2 Answers2