PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Delete not working in Mysql sproc-VBForums
Results 1 to 4 of 4

Thread: Delete not working in Mysql sproc

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2017
    Posts
    33

    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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,783

    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;
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2017
    Posts
    33

    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
    1,431

    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
  •  



Featured


Click Here to Expand Forum to Full Width