0

I need to create a stored procedure in mysql to delete multiple records from a table by providing list of id as a comma separated list.

example

(please note that Id column in the table is integer)

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `deleteLines`(IN $ids varchar(1000))
BEGIN
    select * from line where  $ids in ($ids);
END$$
DELIMITER;

CALL deleteLines('1,3,5,12');

What is the best way to delete multiple records in this scenario?

Nuwan.Niroshana
  • 407
  • 4
  • 15
  • Creation of a sproc is a specific part of the brief? – Strawberry Jan 25 '17 at 00:44
  • Why do you need a procedure for such a simple task? http://stackoverflow.com/questions/2615566/delete-where-id-in-list – Abdelhakim AKODADI Jan 25 '17 at 00:46
  • This stored procedure will call by an application. Rather than calling inline SQL statement from application, I have moved it to a stored procedure. Later if needs a modification it will be easy to do anything without compiling the application. – Nuwan.Niroshana Jan 25 '17 at 01:00

1 Answers1

1

If you have to do this, use find_in_set():

select * from line where find_in_set(id, in_ids);

Actually, the definition should look more like this if you want to delete the records:

DELIMITER $$
CREATE PROCEDURE `deleteLines`(
    IN in_ids varchar(1000)
)
BEGIN
    delete from line
    where find_in_set(id, in_ids);
END$$
DELIMITER;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786