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

the error doesn't match any part of the code you have supplied so far. but it looks like you might have an extra `'` in there — Malachi, Dec 19 '12 at 22:40

score 15 · Accepted Answer · edited May 23 '17 at 12:24

15

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.

edited May 23 '17 at 12:24

Community

1
1

answered Dec 19 '12 at 20:50

Francis P

13,377
3
27
51

It doesn't work after 255 characters is there any way around this? – Colin747 Dec 19 '12 at 21:29
very useful for executing sql via String, instead of direct query. I never knew how to execute with string of sql query. – Lin Dec 23 '17 at 03:06
@Lin Yes it can be very practical if you need to dynamicaly build your query from within the database. – Francis P Mar 13 '18 at 18:54

score 2 · Answer 2 · answered Dec 19 '12 at 21:01

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)

answered Dec 19 '12 at 21:01

Mateusz

99
8

Is there any way around this? – Colin747 Dec 19 '12 at 21:57
does it give you an error about variable size when you run the stored procedure? I don't think that the Variable size is the issue, but i could be wrong – Malachi Dec 19 '12 at 22:11
1

When I run it with 255 characters it works, when I run it with 256 it doesn't. – Colin747 Dec 19 '12 at 23:01
1

yes, its easy define more varchar lenght like Francis wrote ;) – Mateusz Dec 20 '12 at 12:35

Sir Rufo · Answer 3 · 2012-12-21T15:45:43.787

2

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

edited Dec 21 '12 at 15:45

answered Dec 19 '12 at 23:53

Sir Rufo

18,395
2
39
73

they have said that the issue resides in a Variable limit being reached – Malachi Dec 21 '12 at 15:17
1

@Malachi thanx for downvoting, but you will not get an error with my solution, even with the demo data (the string contains 251 characters). I gave a working and simple and `injection-safe` solution – Sir Rufo Dec 21 '12 at 15:51
1

I know this is a very old thread, but its worth mentioning that whilst this does work, its extremely slow due to the fact that FIND_IN_SET doesn't use indexes... So be careful if adding this early on in a project, it'll become more and more of a bottleneck if you use this solution. – Steve Childs Aug 27 '19 at 11:09

MySQL stored procedure that accepts string with multiple parameters

3 Answers3

Linked