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

[Sébastien Lehoux]

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