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.