List:General Discussion« Previous MessageNext Message »
From:W. Scott Hayes Date:May 20 2009 8:05pm
Subject:Stored Procedure Data Types
View as plain text  
Hello,

I would like to do a select on a table to get back the IDs of some of
the records.

Then take those IDs and do a single update using a WHERE clause like
(recordID IN (2,44,21))

My question is:

Can I build a string using a cursor that has all of the IDs and then
issue an update using the string as part of the WHERE clause?

Are there functions that facilitate this better?  I'm wondering if
there is some sort of column function that will grab the IDs from the
initial select.

Below is my code.  Thanks for any advice.

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_getNextQueueBlock$$

CREATE PROCEDURE sp_getNextQueueBlock()
BEGIN
DECLARE l_LinkQueueID INTEGER;
DECLARE no_more_queue_items INT DEFAULT 0;
DECLARE l_updateString VARCHAR(2000) DEFAULT '';
DECLARE queue_csr CURSOR FOR
	SELECT LinkQueueID FROM linkqueue WHERE Completed <> 0 LIMIT 200;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_queue_items=1;

START Transaction;
OPEN queue_csr;
queue_loop:LOOP
	FETCH queue_csr INTO l_LinkQueueID;
	IF no_more_queue_items=1 THEN
		LEAVE queue_loop;
	END IF;
	SET l_updateString=CONCAT(l_updateString,', ',l_LinkQueueID);
END LOOP queue_loop;

IF LENGTH(l_updateString) > 2 THEN
	SET l_updateString=SUBSTRING(l_updateString,3,LENGTH(l_updateString)-2);
END IF;

UPDATE linkqueue SET Completed = 0 WHERE (LinkQueueID IN (l_updateString));
commit;
END$$

DELIMITER ;
Thread
Stored Procedure Data TypesW. Scott Hayes20 May
  • Re: Stored Procedure Data TypesJanek Bogucki22 May