Stored procedure malfunction
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.
The first result returned by the query inside the loop was null, and whatever you concatenate with null will return null.
The second time, it cached the last value of @record_id so it would concatenate until the next time @record_id would be null.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread