www.webdeveloper.com
Results 1 to 2 of 2

Thread: Stored procedure malfunction

Hybrid View

  1. #1
    Join Date
    Mar 2013
    Location
    Iasi, Romania
    Posts
    53

    Stored procedure malfunction

    Hello. So I have this procedure

    Code:
    BEGIN
    	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;
    	EXECUTE stmt1;
    	DEALLOCATE PREPARE stmt1;
    	OPEN cur1;
        read_loop: LOOP
        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;
    		EXECUTE stmt3;
    		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;
    
        END LOOP;
    		
      CLOSE cur1;
    	SELECT _output;
    END
    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.
    I imagine it has something to do with the cache, but I don't have any idea what.

    Any help would be appreciated.
    Thanks.

  2. #2
    Join Date
    Mar 2013
    Location
    Iasi, Romania
    Posts
    53
    Got it.

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles