MySQL Stored Procedure returning multiple record sets

Question

I have created some stored procedures in my database (MySQL) as follows.

Stored Procedure 1

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

Stored Procedure 2

CREATE PROCEDURE sp_Name2(
    param1,
    param2,
    ...... 
)
BEGIN
   .....
   some code
   IF cond THEN 
       SELECT '2' As Result;

       SELECT '3' As Result;
   END IF
END

Now I am calling my first stored procedure as follows:

Call sp_Name1(param1, param2, ... );

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).

How do I suppress unwanted result sets?

EDIT : I have use case for your better understanding.

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 > 0 THEN
           CALL sp_Login(userid);
           SET loginid = LAST_INSERT_ID();         
       END IF;
       //only this result i am expecting.
       IF loginid > 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

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.

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.

user_10164835 · Answer

Return multiple values like

CREATE PROCEDURE `sp_ReturnValue`(
    p_Id int(11),                -- Input param
    OUT r_status1 INT(11)               -- output param
    OUT r_status2 VARCHAR(11)               -- output param
    OUT r_status3 INT(11)               -- output param
)
BEGIN 
    SELECT Status FROM tblUsers WHERE tblUsers_ID = p_Id; // use of input param
      SET r_status1 = 2; // use of output param
      SET r_status2 = "ABCD"; // use of string output param
      SET r_status3 = 2; // use of output param
END

And READ Like

CREATE PROCEDURE `sp_ReadReturnValue`()
BEGIN
    SELECT @ret_value1 AS ret_value1,
           @ret_value2 AS ret_value2,
           @ret_value3 AS ret_value3;
END

Parameter in node for Out param

con.query("CALL sp_ReturnValue(?, @ret_value1, @ret_value2, @ret_value3); CALL sp_ReadReturnValue;", [id], (err, rows) => {
                console.log("Print return value 
 "); 
                console.log(rows[1][0].ret_value1 "
");  
                console.log(rows[1][0].ret_value2 "
");
                console.log(rows[1][0].ret_value3 );
        })

MySQL Stored Procedure returning multiple record sets

6 Answers