title: MySQL Stored Procedure using Input Variable in With Clause
body: <p>Slowly working through change MSSQL database to MySQL and the final problem is as follows :-</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `user_GetUserInRole`(
          IN $EMail               nvarchar(256),
          IN $RoleName            nvarchar(256),
          INOUT $ReturnStatus bit)
  BEGIN
  
      DECLARE $UserId     char(38);
      DECLARE $RoleId        char(38);
      DECLARE $this_count INT;
  
      SET $UserId = (SELECT UserId FROM server.`user_Data` WHERE EMail = $EMail);
      SET $RoleId = (SELECT RoleId FROM server.`user_Roles` WHERE RoleName = $RoleName);
  
      SET $this_count = (SELECT COUNT(*) FROM user_UsersInRoles WHERE UserId = $UserId AND RoleId = $RoleId);
  
      IF ($this_count &gt; 0) THEN
          SET $ReturnStatus = 1;
      ELSE
          SET $ReturnStatus = 0;
      END IF;
  
  END
  </code></pre>
  
  <p>Always returns 0, even when I know the count is equal to 1.  I believe that there is an issue with the input variables not being recognized properly by the Where clause in both <code>Select UserId</code> and <code>Select RoleId</code> statements, but I can't see what I'm doing differently to the various helps I've found.</p>
  
  <p>Any help, as always, is greatly appreciated.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have since tried running a simple insert command</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
  BEGIN
  
      INSERT INTO server.`test_table`
      VALUES('test', 'test2');
  
  END
  </code></pre>
  
  <p>And this hasn't worked either.  This leads me to believe that it's actually more of a problem with the PDO call.</p>
  
  <pre><code>$command = "EXEC test";
  $stpro = $conn-&gt;prepare($command);
  $returnvalue = $stpro-&gt;execute();
  </code></pre>
  
  <p>I know that <code>$conn</code> works as straight sql calls in my php definitely work.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>So, turns out <code>EXEC</code> should have been <code>CALL</code>.  This now works with the test.  As soon as I put any parameters into it though it stops working.  Any thoughts?</p>