Re: ROW_COUNT does not seem to work in a Stored Procedure...

[William Gann]

I had a similar problem; the documentation is not too good for this function.

Add a new variable to store the row count(s) from your statements. Declare it like this:

DECLARE myRowCount int default 0;

This automatically initializes the variable--if you don't initialize a variable in mySQL it will always be NULL!

Next capture the row count immediately after your UPDATE statement--if you do it after the SET, as shown above, it will always return 0 because SET affects no rows. Capture the row count like this:

SELECT row_count() INTO myRowCount;

Now you can do what you like with your myRowCount variable.

PS: You may consider checking on myRowCount = -1 as well as myRowCount = 0. If row_count() returns -1 it means the previous statement encountered an error. Unfortunately there is no way (yet) to capture the error number and act accordingly, but at least you can trap if there is an error (a la EXCEPTION WHEN OTHERS THEN... in Oracle)

Hope this helps someone out there, I struggled with row_count() for quite some time.