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
– Malachi, Commented Dec 19, 2012 at 22:40

Community · Accepted Answer · 2017-05-23 12:24:31Z

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, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 19, 2012 at 20:50

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

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

3 Comments

Colin747 Over a year ago

It doesn't work after 255 characters is there any way around this?

Lin Over a year ago

very useful for executing sql via String, instead of direct query. I never knew how to execute with string of sql query.

Francis P Over a year ago

@Lin Yes it can be very practical if you need to dynamicaly build your query from within the database.

Mateusz · Accepted Answer · 2012-12-19 21:01:03Z

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, 2012 at 21:01

Mateusz

998 bronze badges

4 Comments

Colin747 Over a year ago

Is there any way around this?

Malachi Over a year ago

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

Colin747 Over a year ago

When I run it with 255 characters it works, when I run it with 256 it doesn't.

Mateusz Over a year ago

yes, its easy define more varchar lenght like Francis wrote ;)

Sir Rufo · Accepted Answer · 2012-12-21 15:45:43Z

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, 2012 at 15:45

answered Dec 19, 2012 at 23:53

Sir Rufo

19.3k3 gold badges47 silver badges79 bronze badges

3 Comments

Malachi Over a year ago

they have said that the issue resides in a Variable limit being reached

Sir Rufo Over a year ago

@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

Steve Childs Over a year ago

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.

Collectives™ on Stack Overflow

MySQL stored procedure that accepts string with multiple parameters

3 Answers 3

3 Comments

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related