MySQL stored procedure returning multiple recordsets

Question

I created several 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 call my first stored procedure as follows:

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

Here I get 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 only for the latest result set. Sometimes the sequence of results appears in the expected order, which means that the results can appear as Result 1, Result 2, Result 4, Result 3 (In this case, I can not judge which set of results is useful to me, since the last set of results can be changed )

How to suppress unwanted result sets?

EDIT: I have a precedent 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 requests a login and enters his username with password on my login page, I have a call to sp_LoginUser () on my server. In many cases, I have to call sp_Login () separately.

In the above case, I can set one parameter (for example, loginid) AS INOUT in the sp_Login () procedure, assign it LAST_INSERT_ID (), delete the SELECT statement and get it in sp_LoginUser (). But when I need to call sp_Login () separately, I need to declare some variable in my encoding to get the value.

Answer 1

If you do not need these result sets, do not select them.

Answer 2

When you make a selection inside a stored procedure, the result 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 

Answer 3

Use DO SELECT.. if you do not want to return a result set for selection ( http://dev.mysql.com/doc/refman/5.6/en/do.html ). However, I don’t understand why you start fetching first if you don’t want results.

Answer 4

Why stored procedures? You can do this with regular 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 a login line - you can join it in the user table to also get all user data.

If the username / password is incorrect, you will 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, especially because they are the least portable way (there are other good reasons ); if you can implement in plain SQL this is the best option. Although this SQL is not fully portable, it can be easily converted since most databases have similar functions and functions for mysql.

Answer 5

I'm not sure why you select 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 use a scalar function instead.