Hello. So I have this procedure
The problem is that when I call it for the first time, after I open the database connection, it returns null. After that everything works just fine.
DECLARE _output TEXT DEFAULT '';
DECLARE robotID INT;
DECLARE ind INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT id FROM robots;
SET @count = CONCAT("SELECT count(id) INTO @count_no FROM robots");
PREPARE stmt1 FROM @count;
DEALLOCATE PREPARE stmt1;
FETCH cur1 INTO robotID;
SET @getRecordID = CONCAT("SELECT record_id INTO @record_id FROM ",robotID,"_records WHERE (record_phone1=",phone_number," OR record_phone2=",phone_number," OR record_cellphone =",phone_number,") AND record_invoice_nr= ",invoice_no," LIMIT 1");
PREPARE stmt3 FROM @getRecordID;
DEALLOCATE PREPARE stmt3;
SET ind = ind+1;
SET _output = CONCAT(_output,robotID,' - ',@record_id, "\r\n");
IF ind = @count_no THEN LEAVE read_loop; END IF;
I imagine it has something to do with the cache, but I don't have any idea what.
Any help would be appreciated.