IN() not working as expected in SP

Hi guys,

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).

Cheers guys

Hey.

When you use a user variable like that, it is read as a single entry. Only the first value that can be parsed will be used.
For example, consider this:

mysql> SET @in = '1,2,3';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 3 IN(@in);
+-----------+
| 3 IN(@in) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT 1 IN(@in);
+-----------+
| 1 IN(@in) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Try using a prepared statement, instead of issuing it like normally:

mysql> SET @qeuery = CONCAT('SELECT 3 IN(', @in, ')');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @qeuery;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+-------------+
| 3 IN(1,2,3) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Ah, of course, I’ve already had to use one prepared statement because of the use of a variable LIMIT, I should’ve thought of that. I’ll give that a shot now, thanks

Yup, that did it, thanks :smiley:

No problem :slight_smile: