MySQL stored procedure that accepts string with multiple values in parameters

Question

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?

here when you pass argument to procedure "'London',Lyon'" its take whole string not commas seprated, thats why its 0 rows. — Vipin Jain
– Vipin Jain, Commented Feb 3, 2016 at 5:59
If i remove double quotes then it will take as 2 parameters and throws error. — Lifewithsun
– Lifewithsun, Commented Feb 3, 2016 at 6:00
i haven't given any double quotes in procedure. Could you please suggest example according to my question? — Lifewithsun
– Lifewithsun, Commented Feb 3, 2016 at 6:04

Basile · Accepted Answer · 2016-02-03 11:36:54Z

6

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'") ;

edited Feb 3, 2016 at 11:36

answered Feb 3, 2016 at 6:10

Basile

3361 silver badge7 bronze badges

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

4 Comments

Lifewithsun Over a year ago

Is this the only way to execute the stored procedure? Please suggest any other solution for this.

Lifewithsun Over a year ago

Nothing wrong with this. I just want to know is there any alternative?

Madhumita Over a year ago

This solution is working for me, but in a few seconds it is throwing the following error "Lookup Error - MySQL Database Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Any help how to get around this.

Virendra Singh Rathore Over a year ago

This solution is prone to SQL injection use with better care :)

Virendra Singh Rathore · Accepted Answer · 2020-03-02 14:16:13Z

2

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');

answered Mar 2, 2020 at 14:16

Virendra Singh Rathore

6036 silver badges17 bronze badges

2 Comments

AaronHolland Over a year ago

This should be the accepted answer. Building SQL dynamically is asking for trouble.

Virendra Singh Rathore Over a year ago

Building SQL dynamically is not that bad if we write parameterized query. But after writing it using concat method I find my hands dirty :)

Collectives™ on Stack Overflow

MySQL stored procedure that accepts string with multiple values in parameters

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related