MySQL Forums
Forum List  »  Stored Procedures

problems occurred when process multiple rows in procedure
Posted by: Duy Anh K. Dang
Date: September 19, 2011 02:59AM

Hello everybody,
In my application, I had to write some procedures that allow delete multiple records.
For example, I have table tbl_file_extensions as follow:

CREATE TABLE IF NOT EXISTS `tbl_file_extensions` (
`extension_id` int(11) NOT NULL AUTO_INCREMENT,
`extension_name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`extension_mimetypes` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`extension_description` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`extension_id`),
INDEX `INDEX` (`extension_name` ASC, `extension_mimetypes` ASC)
) ENGINE=InnoDB;

Suppose that there are over 800 rows in this table (id is from 1 to 800)

I write a procedure to delete multiple records that user selected:

CREATE PROCEDURE p_delete_extensions (IN p_strExtIds TEXT, OUT p_intResult INT)
BEGIN
DELETE FROM tbl_file_extensions WHERE extension_id IN (p_strExtIds);
SELECT ROW_COUNT() INTO p_intResult;
END//

Then I call this procedure by way:

CALL p_delete_extensions('1,2,3,4,5', @result);
SELECT @result;

In this case, MySQL only deleted the record with id = 1 (2,3,4,5 were not deleted) and @result = 1;

I changed procedure as follow:

CREATE PROCEDURE p_delete_extensions (IN p_strExtIds TEXT, OUT p_intResult INT)
BEGIN
SET @strSQL = CONCAT('DELETE FROM tbl_file_extensions WHERE extension_id IN (',p_strExtIds,')');
PREPARE stmt FROM @strSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT ROW_COUNT() INTO p_intResult;
END//

The MySQL deleted all of them but @result (ROW_COUNT()) always was 0.
I'm using MySQL version 5.5.8.
I don't know what the problems are? anyone can explain it to me. Thanks a lot.
Sorry for my bad English

Problems solved!

------------------------
Regards,
dkduyanh17@yahoo.com



Edited 2 time(s). Last edit at 09/21/2011 04:38AM by Duy Anh K. Dang.

Options: ReplyQuote


Subject
Views
Written By
Posted
problems occurred when process multiple rows in procedure
2726
September 19, 2011 02:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.