Capturing "Affected Records" in a Stored Procedure

Toad for IBM DB2
1

Is there a way to capture the "Affected Records" in a "Stored Procedure" ?

My Stored Proc does a series of deletions. The Powers that be wants feed back on how many records are affected by each deletion.

TIA

2

Hi,
I just learned a new trick that might help you.

SELECT * FROM OLD TABLE(
delete from anytable where id = -9999999

);

selects all affected rows.

This can be done for an update too.

I used this to prepare undo statements for mass updates.

select "update tablename set is_active = ", is_active , "where id = " id , ";" from old table (
update tablename set is_active = 0 where is_Active = 1

) .

The results returned all rows back to what they were, without updating records that were allready is_active = 0 before I started.
Shimon J.

3

Finally found the answer. Took forever.

Create Procedure PC_DATAFIX.DeleteClientFromCase( In p_cl_id BigInt, In p_case_id BigInt, In Out p_message VarChar(4000) )

Declare v_rows Integer ;
Declare a,b VarChar(100);

Delete From TCL_AGCY_INVLVMNT Where CAS_ID = p_case_id and CL_ID = p_cl_id;
GET DIAGNOSTICS v_rows = ROW_COUNT;
If v_rows > 0 Then Set a = chr(10) || 'TCL_AGCY_INVLVMNT rows deleted ' || Cast(v_rows as VarChar(3)); End If;

Delete From TCL_ADPT_CHLD_RLTN_RSC Where CAS_ID = p_case_id and CL_ID = p_cl_id;
 GET DIAGNOSTICS v_rows = ROW_COUNT;
If v_rows > 0 Then Set b = chr(10) || 'TCL_ADPT_CHLD_RLTN_RSC rows deleted ' || Cast(v_rows as VarChar(3)); End If;

Set p_message = Coalesce(a,'') || Coalesce(b,'');