SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question 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:
    Code MySQL:
    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

  2. #2
    SitePoint Enthusiast Atli's Avatar
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    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)

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yup, that did it, thanks

  5. #5
    SitePoint Enthusiast Atli's Avatar
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •