title: MySQL: Call Stored Procedure from function
body: <p>I've Stored Procedure in MySQL <code>String_Split</code>. </p>
  
  <pre><code>DROP PROCEDURE IF EXISTS String_Split;
  DELIMITER $$
  CREATE PROCEDURE String_Split(vString VARCHAR(8000),vSeparator VARCHAR(5))
  BEGIN
  
  DECLARE vDone tinyint(1) DEFAULT 1;
  DECLARE vIndex INT DEFAULT 1;
  DECLARE vSubString VARCHAR(500);
  
  DROP TEMPORARY TABLE IF EXISTS tmpIDList;
  CREATE TEMPORARY TABLE IF NOT EXISTS tmpIDList (ID varchar(500));
  
  WHILE vDone &gt; 0 DO
    SET vSubString = SUBSTRING(vString, vIndex,
                      IF(LOCATE(vSeparator, vString, vIndex) &gt; 0,
                        LOCATE(vSeparator, vString, vIndex) - vIndex,
                        LENGTH(vString)
                      ));
    IF LENGTH(vSubString) &gt; 0 THEN
        SET vIndex = vIndex + LENGTH(vSubString) + 1;
        INSERT INTO tmpIDList VALUES (vSubString);
    ELSE
        SET vDone = 0;
    END IF;
  END WHILE;
  
  END$$
  DELIMITER ;
  </code></pre>
  
  <p>I'm calling this Stored Procedure from function.</p>
  
  <pre><code>DROP FUNCTION IF EXISTS test;
  DELIMITER $$
      CREATE FUNCTION test (IDLIST VARCHAR(255))
      RETURNS VARCHAR(2000)
      BEGIN
          DECLARE returnData VARCHAR(2000);
  
          DROP TEMPORARY TABLE IF EXISTS TEMP1;       
          CREATE TEMPORARY TABLE IF NOT EXISTS TEMP1(id bigint);
  
          CALL String_Split(IDLIST,',');
          INSERT INTO TEMP1 (SELECT * FROM tmpIDList);
  
          SELECT GROUP_CONCAT(first_name) INTO returnData
          FROM user_master 
          WHERE id IN (SELECT id FROM TEMP1);
  
          RETURN returnData;
      End $$
  DELIMITER ;
  </code></pre>
  
  <p>But I'm getting following exception </p>
  
  <pre><code>Error Code: 1146. Table 'backup25dec_latest.tmpidlist' doesn't exist
  </code></pre>