MySQL stored procedure that accepts string with multiple parameters

Question

I want to create a stored procedure which accepts all the values in the IN parameter as a single string.

DELETE FROM object 
WHERE Type NOT IN 
    ('ListGrid',
     'TextField',
     'SpinBox',
     'MenuButton',
     'ListGrid',
     'RadioButton',
     'DropDown',
     'PopUp',
     'Element',
     'Checkbox',
     'TreeDropDown',
     'TblColumn',
     'Button',
     'Link',
     'Filter',
     'TblRow',
     'GridRow',
     'Popup')

This is an example of one I've tried but it does not work.

DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(255))
BEGIN
SET @query = CONCAT ('DELETE FROM object WHERE Type NOT IN (',p_type,')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''List)' at line 1

When running this query:

CALL deleteObjectTypes("'ListGrid1','TextField1','SpinBox1','MenuButton1','ListGrid2','TextField2','SpinBox2','MenuButton2','ListGrid3','TextField3','SpinBox3','MenuButton3','ListGrid4','TextField4','SpinBox4','MenuButton4','ListGrid5','TextField5','SpinBox5','MenuButton5','ListGrid6','TextField6','SpinBox6','MenuButton6'")

Answer 1

You need to change the VARCHAR size to it's maximum value (or a lower significant value).

DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(65535))
BEGIN
    SET @query = CONCAT ('DELETE FROM object WHERE Type NOT IN (',p_type,')');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

However, note that the limit is lower if you use a multi-byte character set:

VARCHAR(21844) CHARACTER SET utf8

As seen here.

Answer 2

(sorry i cannot add comments too low reputation) Your procedure looks ok, maybe the problem is somewhere else? note that we have defined as a varchar 255 characters and the example you provided more than this number (291 characters)

Answer 3

You should give this a try (shortened example):

DELETE 
FROM 
  object 
WHERE 
  NOT FIND_IN_SET( Type, 'ListGrid,TextField,SpinBox,MenuButton,ListGrid' );

and with stored procedure

DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(255))
BEGIN
  DELETE 
  FROM 
    object 
  WHERE 
    NOT FIND_IN_SET( Type, p_type );
END //
DELIMITER ;

---

CALL deleteObjectTypes( 'ListGrid1,TextField1,SpinBox1,MenuButton1,ListGrid2,TextField2,SpinBox2,MenuButton2,ListGrid3,TextField3,SpinBox3,MenuButton3,ListGrid4,TextField4,SpinBox4,MenuButton4,ListGrid5,TextField5,SpinBox5,MenuButton5,ListGrid6,TextField6,SpinBox6,MenuButton6' );