Get diagnostics

[sriramyait]

create procedure sam() language sql
BEGIN
declare numrows integer default 0;
update sri set id=95 where id =99;
commit;
GET DIAGNOSTICS numrows = ROW_COUNT;
UPDATE sri1 SET id =numrows WHERE id =0;
commit;
END

in the above procedure the updated rows is numrows .....but i m getting 0 for numrows ..............c an anyone tell the reason????????? Please help!!!!!!!!!!!

[cburnett]

Commit is an SQL statement too...

So you are getting 0 rows updated because commit does not update any rows. Try removing the commit (or performing the GET DIAGNOSTICS immediately after the update). This returns 2 correctly.