I’ve been trying to write a stored procedure to replace an old iHTML script that we use to clean up our database every day. I’ve written it and am happy with it for the most part, but despite having created an “IN()” parameter I’m only ever able to process the first one and I’ve no idea why. I’ve created a ‘debug’ table that I’m capturing various data in, including the variable that is used in “IN()” but it just doesn’t work. However, if I copy the generated “IN()” function and use that at a command line it works as expected and processes every ID in the parameter.
Here is the SP:
BEGIN # Variable declarations DECLARE `i` INT(10) DEFAULT 0; DECLARE `x` INT(10) DEFAULT 0; DECLARE `cc` INT(10) DEFAULT 0; SET @id = NULL; SET @inva = NULL; # Firstly, find how many records may need to be processed and store in x SELECT COUNT(*) INTO x FROM `customers` WHERE (`email` IS NULL OR `email` = '') AND `dateadded` < DATE_SUB(CURDATE(), INTERVAL 30 DAY); # Now check whether the rlimit parameter specifies that we wish to process # a smaller number than x, and if so, change x to = rlimit IF rlimit < x THEN SET x = rlimit; END IF; # Now, get the ids of each and store in @invar in a suitable format # for use in an IN() function by concatentating with CONCAT_WS WHILE i < x DO SET @a = CONCAT( "SELECT `id` INTO @id FROM `customers` WHERE (`email` IS NULL OR `email` = '') AND `dateadded` < DATE_SUB(CURDATE(), INTERVAL 30 DAY) ORDER BY `id` LIMIT ",i,",1;" ); PREPARE stmt FROM @a; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @inva = CONCAT_WS(',', @inva, @id); SET i = i + 1; END WHILE; # Now use @inva to hide orders for invalid customers UPDATE orders SET custid=0 WHERE approvalcode != 'Email sent' AND custid IN (@inva); # Now use @inva to delete baskets for invalid customers DELETE FROM basket WHERE custid IN (@inva); # Now use @inva to delete invalid customers DELETE FROM customers WHERE id IN (@inva); # Check for remaining invalid customers SELECT COUNT(id) INTO cc FROM customers WHERE email IS NULL AND dateadded < DATE_SUB(CURDATE(), INTERVAL 30 DAY); # For debug purposes, record @inva in to the test table INSERT INTO `db_tidy_debug` (`param1`, `count`, `invar`, `remain`) VALUES (rlimit, x, @inva, cc); END
Any ideas what I’m doing wrong (apart from probably writing the SP horribly).