Mysql multiple parameters with multiple queries inside stored procedure

Question

I need to create a 'select' mysql procedure that will accept multiple parameters, inline with this the procedure will select to other tables

Objective

Stored procedure must accept multiple parameters
Using the multiple parameters, procedure should select on table_a, table_b and table_c

Currently i am using this code (it accepts multiple parameter but I don't know how to modify it so it would do another select on table_b and table_c)

DELIMITER //

CREATE PROCEDURE select_multiple_object(IN user_ids VARCHAR(65535))
BEGIN
    SET @query = CONCAT ('SELECt * FROM table_a WHERE userid IN (',user_ids,')');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

What I really want to achieve is something like this:

DELIMITER //

CREATE PROCEDURE select_multiple_object(IN user_ids VARCHAR(65535))
BEGIN
    SET @query = CONCAT ('
        SELECt * FROM table_a WHERE userid IN (',user_ids,');
        SELECt * FROM table_b WHERE userid IN (',user_ids,');
        SELECt * FROM table_c WHERE userid IN (',user_ids,');
    ');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;



CALL select_multiple_object('1,2,3')

CH3M · Accepted Answer · 2014-09-22 03:29:22Z

0

Assemble your MySQL query first, then send the query, similar to the following:

$parameter = "user_ids";
$table = "table_a";
$query = 'SELECT * FROM ' . $parameter . ' WHERE userid IN (",' . $parameter . ',")';
$result = mysql_query($query);

To input multiple parameters, simply use a loop to repeat this process as needed.

answered Sep 22, 2014 at 3:29

CH3M

617 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mpalencia Over a year ago

thanks for the answer but needs to do this in a mysql stored procedure way

CH3M Over a year ago

Ah I see you've removed the PHP tag, that's what lead me to believe you were using PHP ;)

mpalencia Over a year ago

Yes I removed it, sorry for wasting your time, any thanks friend :)

Collectives™ on Stack Overflow

Mysql multiple parameters with multiple queries inside stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related