-
Apr 30th, 2018, 08:39 AM
#1
Thread Starter
Lively Member
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;
-
Apr 30th, 2018, 09:14 AM
#2
Re: Delete not working in Mysql sproc
Please wrap code snippets in appropriate formatting tags for readability
sql Code:
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;
-
Apr 30th, 2018, 09:39 AM
#3
Thread Starter
Lively Member
Re: Delete not working in Mysql sproc
-
Apr 30th, 2018, 10:08 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|