Results 1 to 4 of 4

Thread: Delete not working in Mysql sproc

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Delete not working in Mysql sproc

    Hi All

    The following sproc physically deletes the record provided that it is the last document
    of its document prefix in the table, if it is not the last record then it records the document as being
    marked for deletion using the user's unique identity.
    But for some reason that is unknown to me the delete command does not execute
    no Error is shown, but no deletion takes place

    Any help would be greatly appreciated or any advice on how to do it better

    Many thanks


    CREATE DEFINER = 'root'@'localhost' PROCEDURE `delete_document_header`(
    IN sp_document_number VARCHAR(12),
    IN sp_user_id INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
    BEGIN
    DECLARE highestnumber VARCHAR(12);
    DECLARE documentprefix CHAR(5);
    set documentprefix = SUBSTR(sp_document_number,0,5);
    set highestnumber=(select MAX(`document_header`.`document_number`) from `document_header`
    where `document_header`.`document_number` Like CONCAT(documentprefix,'%'));
    if highestnumber=sp_document_number then
    DELETE from `document_header` where `document_header`.`document_number`=sp_document_number;
    ELSE
    UPDATE `document_header` set `document_header`.`deleted`=sp_user_id where `document_header`.`document_number`=sp_document_number;
    END IF;
    END;

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Delete not working in Mysql sproc

    Please wrap code snippets in appropriate formatting tags for readability
    sql Code:
    1. CREATE DEFINER = 'root'@'localhost' PROCEDURE `delete_document_header`(
    2.         IN sp_document_number VARCHAR(12),
    3.         IN sp_user_id INTEGER(11)
    4.     )
    5.     NOT DETERMINISTIC
    6.     CONTAINS SQL
    7.     SQL SECURITY DEFINER
    8.     COMMENT ''
    9. BEGIN
    10. DECLARE highestnumber VARCHAR(12);
    11. DECLARE documentprefix CHAR(5);
    12. set documentprefix = SUBSTR(sp_document_number,0,5);
    13. set highestnumber=(select MAX(`document_header`.`document_number`) from `document_header`
    14. where `document_header`.`document_number` Like CONCAT(documentprefix,'%'));
    15. if highestnumber=sp_document_number then
    16. DELETE from `document_header` where `document_header`.`document_number`=sp_document_number;
    17. ELSE
    18. UPDATE `document_header` set `document_header`.`deleted`=sp_user_id where `document_header`.`document_number`=sp_document_number;
    19. END IF;
    20. END;

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Delete not working in Mysql sproc

    my apologies, thank you

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Delete not working in Mysql sproc

    Have you checked for the sp_document_number if sp_user_id is set in your table?
    if yes, then your procedure forks into the UPDATE-part, and no error is thrown, since your procedure had/used a legal exit

    EDIT: btw i just saw that you have COMMENT ''
    isn't that colliding with the '%' in your concat?
    Last edited by Zvoni; Apr 30th, 2018 at 10:12 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width