title: MySQL Stored Procedure Creation with Input and Output Parameters
body: <hr>
  
  <h2><strong>Answer</strong>: <a href="https://stackoverflow.com/a/24034862/1173155">https://stackoverflow.com/a/24034862/1173155</a></h2>
  
  <hr>
  
  <p>I am testing a Stored procedure as follows:</p>
  
  <pre><code>DELIMITER $$
  CREATE PROCEDURE `test1`(IN `tab_name` VARCHAR(40), IN `value_of` VARCHAR(40), OUT `the_id` INT(1))
  BEGIN
   SET @t1=CONCAT('
      BEGIN 
          IF EXISTS (SELECT ',tab_name,'.id from ',tab_name,' where ',tab_name,'.',tab_name,' = ',value_of,')
              THEN
              select id into ',the_id,' from ',tab_name,' where ',tab_name,'.',tab_name,' = ',value_of,';
          ELSE 
              insert into ',tab_name,' values (NULL,',value_of,');
  END IF;');
   PREPARE stmt3 FROM @t1;
   EXECUTE stmt3;
   DEALLOCATE PREPARE stmt3;
  END $$
  DELIMITER ;
  </code></pre>
  
  <p>Trying to select the id, or insert. I have a single row in the table, when I try and use this Procedure as is; it doesn't return anything.</p>
  
  <p>The identifier for the value I want is the table name for simplicities sake. Have spent many hours and I'm at a loss.</p>
  
  <p>Have tried everything, even now just thought adding the NULL, would work as the table has two values and I had forgotten to put it in only to realise I was trying it within the first if clause... IE, entering data to ensure that the first statement would be invoked</p>
  
  <p>Thanks for any help</p>
  
  <hr>
  
  <h1>Update</h1>
  
  <p>Still having trouble with this. Trying to make it as simple as possible now. </p>
  
  <p>I have a city table, with 1 record it in. Structure is like so <code>city.city = 'Dublin'</code></p>
  
  <p>Value and table name are the same. The follow select statement works when executed via phpMyAdmin</p>
  
  <pre><code>BEGIN
       SET @t1= CONCAT("
  
           SELECT id 
               FROM ",tab_name," 
                       WHERE ",tab_name," = '",value_of,"'
  
  
                       ");
       PREPARE stmt3 FROM @t1;
       EXECUTE stmt3;
       DEALLOCATE PREPARE stmt3;
  END
  </code></pre>
  
  <p>Now I just need to ensure that if the select statement doesn't return anything, then insert the value and return the select statement.</p>
  
  <p>This seems like such a simple problem, yet I can't find the solution anywhere</p>
  
  <p>--</p>
  
  <p><code>INSERT IGNORE</code> is not good because it increments the ID regardless of inserting. Is there a work around for this because I could just use that then.</p>
  
  <p><code>INSERT IGNORE</code>...  // Without increment?</p>
  
  <p><code>SELECT ^</code> ....</p>
  
  <hr>
  
  <h2>Further Update</h2>
  
  <p>My procedure now looks like this (takes two VARCHAR parameters) and this is currently working. It doesn't increment the ID if the value already exists either which is simply fantastic.</p>
  
  <pre><code>BEGIN
   SET @t1= CONCAT("INSERT INTO ",tab_name,"(",tab_name,")
  SELECT '",city_name,"' FROM dual
   WHERE NOT EXISTS (SELECT 1 
      FROM ",tab_name," 
       WHERE ",tab_name," = '",city_name,"');
          ");
     PREPARE stmt3 FROM @t1;
       EXECUTE stmt3;
       DEALLOCATE PREPARE stmt3;
      END
  </code></pre>
  
  <p>All I want now is for a simple SELECT statement to return the id of the row with that city name. eg <code>SELECT id FROM tab_name WHERE tab_name = 'city_name';</code></p>
  
  <p>But adding this in causes an error :( Thanks if anyone has a solution to this ridiculous problem</p>