Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,982
    Thanks
    120
    Thanked 76 Times in 76 Posts

    delete, in, stored proc

    this is in stored proc
    Code:
    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 ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    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
    Code:
        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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    Here, for example, is one way to do it:

    Code:
    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 ;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,982
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by Old Pedant View Post
    You say this is inside a stored procedure?
    When you use it in the SP, you are effectively doing
    Code:
        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 (-:
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    Quote Originally Posted by BubikolRamios View Post
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •