title: MySQL Stored Procedure returning multiple record sets
body: <p>I have created some stored procedures in my database (MySQL) as follows.</p>
  
  <p>Stored Procedure 1</p>
  
  <pre><code>CREATE PROCEDURE sp_Name1(
      param1,
      param2,
      ...... 
  )
  BEGIN
     .....
     some code
     IF cond THEN 
         call sp_Name2  //Calling 2nd procedure from here.
  
         Update SomeTable .....
  
         SELECT '1' As Result;
     END IF
  END
  </code></pre>
  
  <p>Stored Procedure 2</p>
  
  <pre><code>CREATE PROCEDURE sp_Name2(
      param1,
      param2,
      ...... 
  )
  BEGIN
     .....
     some code
     IF cond THEN 
         SELECT '2' As Result;
  
         SELECT '3' As Result;
     END IF
  END
  </code></pre>
  
  <p>Now I am calling my first stored procedure as follows:</p>
  
  <pre><code>Call sp_Name1(param1, param2, ... );
  </code></pre>
  
  <p>Here I am getting 4 result sets in MySQL Workbench. 2 results from sp_Name2, 3rd for the update statement in sp_Name1 and 4th from the select statement, also in sp_Name1. Here I am looking for just the last result set. Sometimes the result sequence appears in the expected order, which means the results may come in like Result 1, Result 2, Result 4, Result 3 (In this case I can not judge which result set is useful to me, because last result set may be changed).   </p>
  
  <p>How do I suppress unwanted result sets?</p>
  
  <p>EDIT : 
  I have use case for your better understanding.</p>
  
  <pre><code>CREATE PROCEDURE sp_LoginUser( IN Username  varchar(50) , IN password varchar(50) )
  BEGIN
      IF EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password)
         SET userid = 0;
         SET loginid = 0;
         SELECT userid INTO userid
         FROM users
         WHERE name = UserName and Pwd = password;
         IF userid &gt; 0 THEN
             CALL sp_Login(userid);
             SET loginid = LAST_INSERT_ID();         
         END IF;
         //only this result i am expecting.
         IF loginid &gt; 0 THEN
             SELECT userid as userid, loginid AS loginid;
         ELSE
             SELECT 0 userid, 0 loginid;
         END IF;
      END IF;
  END
  
  CREATE PROCEDURE sp_Login( IN Userid int )
  BEGIN
      INSERT Logins ( userid, datetime )
      VALUES ( Userid, now() );
  
      SELECT LAST_INSERT_ID() AS loginid;
  END
  </code></pre>
  
  <p>So, Now when my user requesting for login and enter his/her username with password on my login page, then I have call sp_LoginUser() on my server. In many cases I have to call sp_Login() separately.</p>
  
  <p>In above case I can set one parameter (eg. loginid) AS INOUT in sp_Login() procedure, assign LAST_INSERT_ID() to it, remove SELECT statement and retrieve in sp_LoginUser().
  But when I need to call sp_Login() separately, i must have to declare some variable in my coding to retrieve value.     </p>