CODEWITHSUNDEEP
mysqlsqlstored-procedures
mpalencia
mpalencia

Reputation: 6007

Mysql multiple parameters with multiple queries inside stored procedure

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

Objective

  1. Stored procedure must accept multiple parameters
  2. 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')

Upvotes: 1

Views: 1668

Answers (1)

CH3M
CH3M

Reputation: 61

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.

Upvotes: 0

Related Questions