Skip navigation links

MySQL Forums :: Stored Procedures :: problems occurred when process multiple rows in procedure


Advanced Search

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 1911 Duy Anh K. Dang 09/19/2011 02:59AM
Re: problems occurred when process multiple rows in procedure 677 Rick James 09/20/2011 09:11PM
Re: problems occurred when process multiple rows in procedure 488 Duy Anh K. Dang 09/21/2011 04:34AM
Re: problems occurred when process multiple rows in procedure 502 Rick James 09/22/2011 03:39PM
Re: problems occurred when process multiple rows in procedure 526 Duy Anh K. Dang 09/24/2011 10:01AM


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.