delete, in, stored proc

this is in stored proc

delete from galery_optional_inf
where id_galery = i_id_galery
and id in (c_optional_inf_remove);


it does not do anything, but it should, and sure is executed.

If I do that, and fill variables (which I catch inside stored proc and save them into test table, so they are 100% the right ones) manualy in, like sql browser it works, it deletes.

id and id_galery are integers and value of variables like
c_optional_inf_remove: 15,16,23
i_id_galery: 34

Any hints ?

You say this is inside a stored procedure?

Then is c_optional_inf_remove an argument/parameter to the procedure??

If so, then I'd guess it is a varchar() parameter, yes? In which case you can't do it this way.

Let's say that c_optional_inf_remove is the string "3,17,20".

When you use it in the SP, you are effectively doing
and id in( '3,17,20' )

You see it? The string is a *SINGLE VALUE*, not a set of values.

This can be done in MySQL, and I know two or three ways to do it, but they are all ugly and slow. This is one case where *NOT* using a stored procedure will almost surely give better performance.

Here, for example, is one way to do it:


DELIMITER //
CREATE PROCEDURE funDelete( i_id_galery INT, c_optional_inf_remove VARCHAR(255) )
BEGIN
SET @sql = CONCAT( 'delete from galery_optional_inf where id_galery = ',
i_id_galery,
' and id in (', c_optional_inf_remove, ')'
);

PREPARE delstmt FROM @sql;
EXECUTE delstmt;
DEALLOCATE PREPARE delstmt;

END\\

DELIMITER ;

You say this is inside a stored procedure?
When you use it in the SP, you are effectively doing
and id in( '3,17,20' )

You see it? The string is a *SINGLE VALUE*, not a set of values.


Thanks for pointing that out.
I figured prepared statement workaround myself, but had no idea why it is working (-:

I figured prepared statement workaround myself

Very nice! That's a tough one to figure out.

The other way it can be done is via a temporary table.