CODEWITHSUNDEEP
mysqlstored-procedures
flyclassic
flyclassic

Reputation: 2499

how to use WHERE IN mysql stored procedure

How do I pass an array and use WHERE IN inside stored procedure?

Do i need to concatenate input string or something ?

Lets say

DELIMITER $$
DROP PROCEDURE IF EXISTS `abc`.`table1`$$
CREATE PROCEDURE  `abc`.`test`
(IN somestring VARCHAR(255))
BEGIN
    SELECT * FROM abc.table1 
    WHERE flight_type IN somestring
END $$
DELIMITER ;

Upvotes: 6

Views: 7831

Answers (4)

Chad E.
Chad E.

Reputation: 1226

The accepted answer needed modification to work with my version of MariaDB.

The CONCAT statement was missing a semicolon, The EXECUTE statement was called on @s (a variable) rather than stmt (a statement), and the drop/create portion was confusing. Here's my rewrite:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dbname`.`procedurename`$$
CREATE PROCEDURE  `dbname`.`procedurename`
(IN somestring VARCHAR(255))
BEGIN
    SET @s=CONCAT("
    SELECT * FROM dbname.table1 
    WHERE flight_type IN (",somestring,");");
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END $$
DELIMITER ;

Upvotes: 1

Pentium10
Pentium10

Reputation: 207982

You can use the string concatenation and the PREPARE statement to run dynamically built queries.

somestring must be constructed in a valid SQL format like '1','2','3'

DELIMITER $$
DROP PROCEDURE IF EXISTS `abc`.`table1`$$
CREATE PROCEDURE  `abc`.`test`
(IN somestring VARCHAR(255))
BEGIN
    @s=CONCAT("
    SELECT * FROM abc.table1 
    WHERE flight_type IN (",somestring,");")
    PREPARE stmt FROM @s;
    EXECUTE @s;
END $$
DELIMITER ;

Upvotes: 4

The Scrum Meister
The Scrum Meister

Reputation: 30131

You can use FIND_IN_SET() if somestring is formatted a,b,c,d:

SELECT *
FROM abc.table1
WHERE FIND_IN_SET(flight_type, somestring)

Upvotes: 3

Bala R
Bala R

Reputation: 108977

This should work as long as somestring is of the form

"(item1, item2, item3, ... )"

if not, you could format it accordingly inside of your SP but I'm not sure what the best practice is.

Upvotes: 0

Related Questions