Skip navigation links

MySQL Forums :: Stored Procedures :: ROW_COUNT does not seem to work in a Stored Procedure...


Advanced Search

ROW_COUNT does not seem to work in a Stored Procedure...
Posted by: ()
Date: June 07, 2005 03:20PM

Hi there,

I have this Stored Procedure that updates a table. While stille inside this stored procedure, after I have updated a row, I need to determine if the row has indeed been updated.

I though after looking at the documentation that the ROW_COUNT() function would do the trick. Unfortunately, it seems that this function always returns 0 as the value for the number of updated rows. Here is my code:

CREATE PROCEDURE PRC_UpdateClients
(
P_ID int,
P_CalculTPS bit,
P_Folio tinyint,
P_Salary numeric(19,4),
P_ClientDate datetime,
P_MilesTraveled float,
P_DistanceWalked smallint,
P_TaxesPayed int,
P_CommissionPercentage real,
P_CodeClient varchar(25),
P_Nom varchar(50),
P_NoTVQ varchar(25),
P_Produit int,
P_TaxeProv int,
P_TaxeFed int,
P_Devise int,
P_Vendeur int,
P_CreatedBy varchar(100),
P_CreatedDate datetime,
P_UpdatedBy varchar(100),
P_UpdatedDate datetime,
P_DeletedBy varchar(100),
P_DeletedDate datetime,
out P_RETURNVALUE int
)
BEGIN

UPDATE
Clients
SET
CalculTPS = P_CalculTPS,
Folio = P_Folio,
Salary = P_Salary,
ClientDate = P_ClientDate,
MilesTraveled = P_MilesTraveled,
DistanceWalked = P_DistanceWalked,
TaxesPayed = P_TaxesPayed,
CommissionPercentage = P_CommissionPercentage,
CodeClient = P_CodeClient,
Nom = P_Nom,
NoTVQ = P_NoTVQ,
Produit = P_Produit,
TaxeProv = P_TaxeProv,
TaxeFed = P_TaxeFed,
Devise = P_Devise,
Vendeur = P_Vendeur,
UpdatedBy = P_UpdatedBy,
UpdatedDate = NOW(),
DeletedBy = P_DeletedBy,
DeletedDate = P_DeletedDate
WHERE
ID = P_ID AND
UpdatedDate = P_UpdatedDate AND
DeletedDate IS NULL;

---------------------------------------
-- Manages concurrency
---------------------------------------
SET P_RETURNVALUE = 0;

IF ROW_COUNT() = 0 THEN
SET P_RETURNVALUE = -7004;
ELSE
-- Reads the updated record and returns it
CALL PRC_ReadClients(P_ID);
END IF;

END

//

Is it a bug or am I using the Function wrong?

Thanks in advance!

Sebastien Lehoux

Options: ReplyQuote


Subject Views Written By Posted
ROW_COUNT does not seem to work in a Stored Procedure... 6501 06/07/2005 03:20PM
Re: ROW_COUNT does not seem to work in a Stored Procedure... 3979 William Gann 07/31/2007 07:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.