title: How to use a sub query as part of a stored procedure MySQL
body: <p>I am trying to create a procedure (called a routine in phpMyAdmin) and currently have this query:</p>
  
  <pre><code>BEGIN
  
  INSERT INTO `users` (`username`, `password`, `email`, `user_role_id`,` date_registered`)
      VALUES (_username, _password, _email, (SELECT `ID` FROM `roles` WHERE `role_name` = _role) , _date);
  
  END;
  </code></pre>
  
  <p>But this does not work, I have tried to declare a variable to a role_ID and this also did not work, can anyone help me to fix this please?</p>
  
  <p><strong><em>NOTE</em></strong></p>
  
  <p>The params you see (starting with an underscore (<code>_</code>)) are all <em>IN</em> params used in the phpMyAdmin routine creator</p>
  
  <p><strong><em>EDIT</em></strong></p>
  
  <p>The other code I have tried is this:</p>
  
  <pre><code>BEGIN
  
  DECLARE role_id AS/*=*/ (SELECT `ID` FROM `roles` WHERE `role_name` = _role); /* tried both AS and = */
  
  INSERT INTO `users` (`username`, `password`, `email`, `user_role_id`, `date_registered`)
      VALUES (_username, _password, _email, role_id, _date);
  
  END;
  </code></pre>
  
  <p>Also noticed a small error with the <code>DECLARE</code>, changed the above code to:</p>
  
  <pre><code>BEGIN
  
  DECLARE @role_id ;
  SET @role_id = (SELECT `ID` FROM `roles` WHERE `role_name` = _role);
  
  INSERT INTO `users` (`username`, `password`, `email`, `user_role_id`, `date_registered`)
      VALUES (_username, _password, _email, @role_id, _date);
  
  END;
  </code></pre>
  
  <p>But still no luck</p>