Select ROW_COUNT() INTO a variable

--=-Y1owxZoWSjJEbUYqz3Mb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

I have a stored procedure in mysql 5.1.48 that deletes old data from my
tables.
I would like to keep a running count while it does this. Here is what I
try now:
....
DECLARE dropCnt INT DEFAULT 0;
....
SET @sql = CONCAT('DELETE FROM myTable WHERE itemid = ', iID, ' AND
clock BETWEEN 0 AND ', histUnixTime, ' ');
PREPARE s1 FROM @sql;
EXECUTE s1;
SELECT ROW_COUNT() INTO dropCnt;
DEALLOCATE PREPARE s1;
....

The problem here is that "SELECT ROW_COUNT() INTO dropCnt;" returns NULL
every time.
If I just do the select without the INTO, I get 0 or some actual count
of rows.
How can I get my dropCnt variable correcly set?

Thanks,
Bryancan



--=-Y1owxZoWSjJEbUYqz3Mb--

Re: Select ROW_COUNT() INTO a variable

On 7/19/2010 5:22 PM, Bryan Cantwell wrote:
> I have a stored procedure in mysql 5.1.48 that deletes old data from my
> tables.
> I would like to keep a running count while it does this. Here is what I
> try now:
> ...
> DECLARE dropCnt INT DEFAULT 0;
> ...
> SET @sql = CONCAT('DELETE FROM myTable WHERE itemid = ', iID, ' AND
> clock BETWEEN 0 AND ', histUnixTime, ' ');
> PREPARE s1 FROM @sql;
> EXECUTE s1;
> SELECT ROW_COUNT() INTO dropCnt;
> DEALLOCATE PREPARE s1;
> ...
>
> The problem here is that "SELECT ROW_COUNT() INTO dropCnt;" returns NULL
> every time.
> If I just do the select without the INTO, I get 0 or some actual count
> of rows.
> How can I get my dropCnt variable correcly set?
>

You may be able to use one of the other two syntaxes available:

1) SET dropCnt = ROW_COUNT()

2) SELECT dropCnt := ROW_COUNT()

Please let us know if either of those also fails.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org