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.

Solution 1

If you don't want those result sets, don't select them.

Solution 2

When you perform a select inside a stored procedure the resultset is returned to the client. http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html#qandaitem-B-4-1-14

CREATE PROCEDURE sp_LoginUser( IN Username  varchar(50) , IN password varchar(50) )
BEGIN
    --put the resultset into a variable so it don't return back
    DECLARE doesUserExist BOOL;
    SELECT EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password ) INTO doesUserExist;
    IF doesUserExist
       SET userid = 0;
       SET loginid = 0;
       SELECT userid INTO userid
       FROM users
       WHERE name = UserName and Pwd = password;
       IF userid > 0 THEN
           -- call a function instead of a procedure so you don't need to call last_insert_id again
           SET loginid = sp_Login(userid);
       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
-- this is now a function so it can return what you need
CREATE FUNCTION sp_Login(Userid int) 
RETURNS INTEGER
BEGIN
    INSERT Logins ( userid, datetime )
    VALUES ( Userid, now() );

    SET loginid = LAST_INSERT_ID();
    RETURN loginid;
END

Solution 3

Use DO SELECT.. if you don't want to return a result set for a select (http://dev.mysql.com/doc/refman/5.6/en/do.html). However, I don't understand why you run the selects in the first place if you don't want the results.

Solution 4

I'm not sure why you are selecting LAST_INSERT_ID() in sp_Login and again in sp_LoginUser?

If you need to return LAST_INSERT_ID() from sp_Login you need to either assign an output variable to it or consider using a scalar function instead.

Solution 5

Why the stored procs? You can do it with normal SQL:

SET @previous := LAST_INSERT_ID();

INSERT INTO Logins ( userid, datetime )
SELECT Userid, now()
FROM users
WHERE name = UserName
and Pwd = password;

SELECT *
FROM Logins
WHERE ID = LAST_INSERT_ID()
AND ID != @previous;

If the username/password was correct, your rowset will have the row for the login - you could join it to the user table to also get all the user data too.

If the username/password was incorrect, you'll have an empty rowset.

FYI, LAST_INSERT_ID() returns 0 if there are no previous inserts.

---

Stored procedures are the least preferred way to implement SQL, particularly because they are the least portable way (there are other good reasons too); if you can implement in plain SQL it's a better option. Although this SQL isn't completely portable, it can be converted reasonably easily as most databases have similar functions and features to mysql's.

View more solutions