Return result set in OUT parameter of Stored Procedure in MySQL

Question

I have procedure in MySQL which has two IN parameters: userLogin(VARCHAR) and userPassword(VARCHAR), and two OUT parameters: userID (INT) and userRights(VARCHAR).

The procedure works as follows: check, if user with given login and password is in the database, if so, return his ID, userRights and settings. Rights can be concatenated into a string, but i would like to avoid concatenating and subsequently parsing websettings, since the structure of the data is more complicated.

Now, my procedure returns only userID and userRights and if succesfull, I run another select statement to get the settings.

Current procedure:

DELIMITER $$
CREATE PROCEDURE `proc_login_user`(IN UserName VARCHAR(45), IN Pwd VARCHAR(250), OUT Uid INT, OUT Rights VARCHAR(100))
BEGIN
    DECLARE Uname VARCHAR(45);
    DECLARE Pass VARCHAR(250);
    DECLARE UserId INT;
    SET @Uname := UserName;
    SET @Pass := Pwd;
    SET @UserId = (SELECT ID FROM `users` WHERE Login = @Uname AND Password = @Pass);    
    SET Uid = @UserId;
    SET Rights = /* some SELECT GROUP_CONCAT statement to create string */
END;

I wish to add one more output parameter to procedure: userSettings, which should be a result set.

Something like this:

DELIMITER $$
CREATE PROCEDURE `proc_login_user`(IN UserName VARCHAR(45), IN Pwd VARCHAR(250), OUT Uid INT, OUT Rights VARCHAR(100), OUT Settings)
BEGIN
    DECLARE Uname VARCHAR(45);
    DECLARE Pass VARCHAR(250);
    DECLARE UserId INT;
    SET @Uname := UserName;
    SET @Pass := Pwd;
    SET @UserId = (SELECT ID FROM `users` WHERE Login = @Uname AND Password = @Pass);    
    SET Uid = @UserId;
    SET Rights = /* some SELECT GROUP_CONCAT statement to create string */
    SET Settings = SELECT * FROM `settings` WHERE UserID = @UserId;
END;

Any help would be appreciated.

Thanks, Zbynek

Answer 1

MySQL does not permit variables to contain a result set or table; they are single scalar values only.

Within a stored procedure, you can simply run a SELECT, and the result set will be returned to the client. You can't use that result set within other stored routines inside MySQL, but an application that called the procedure could consume it just like any other query result.

As an aside:

DECLARE Uname VARCHAR(45);
SET @Uname := UserName;

These two lines have nothing to do with each other. The local variable 'Uname' and the user variable '@Uname' are completely separate things. In this particular case, you don't need either one. You can refer to IN/OUT variables directly in queries (just make sure those variables are not the same as the name of a column in the table, or you may get unexpected results.)

DELIMITER $$
CREATE PROCEDURE `proc_login_user`(IN UserName VARCHAR(45), IN Pwd VARCHAR(250), OUT Uid INT, OUT Rights VARCHAR(100))
BEGIN
    SELECT ID INTO Uid FROM `users` WHERE Login = UserName AND Password = Pwd;    
    /* some SELECT GROUP_CONCAT INTO Rights statement to create string */
    SELECT * FROM `settings` WHERE UserID = Uid;
END;