CODEWITHSUNDEEP
mysqlstored-procedures
Lifewithsun
Lifewithsun

Reputation: 998

MySQL stored procedure that accepts string with multiple values in parameters

I want best way to call store procedure where i have multiple values in single parameter like City parameter having values like 'london','lyon','kln' and many more.

My stored procedure is like this

CREATE PROCEDURE `GetCityEmpData`(IN `City` VARCHAR(64))
BEGIN 
   SELECT * FROM Employees 
   WHERE Employees.City in (City);
END

and call it like this

call GetCityEmpData("'London',Lyon'") ;

it returns 0 rows where there is data for the given parameters. Is it possible to execute the same without prepare statement?

Upvotes: 2

Views: 8573

Answers (2)

Virendra Singh Rathore
Virendra Singh Rathore

Reputation: 603

You can do this using find_in_set method

CREATE PROCEDURE `GetCityEmpData`(IN `City` VARCHAR(64))
BEGIN 
   SELECT * FROM Employees 
   WHERE FIND_IN_SET (Employees.City ,City);
END

and call it like 

call GetCityEmpData('London,Lyon');

Upvotes: 2

Basile
Basile

Reputation: 336

You could try this :

CREATE PROCEDURE `GetCityEmpData`(`City` VARCHAR(64))
BEGIN
   set @query = concat("SELECT * FROM Employees 
                        WHERE Employees.City in (" , City , ")");

    PREPARE stmt FROM @query;
    EXECUTE stmt ;
END

then you can call your procedure as you wanted :

call GetCityEmpData("'London','Lyon'") ;

Upvotes: 6

Related Questions