how to insert no of rows effected by a sql stmt in bteq script into a table with in the same script

Tools & Utilities
Enthusiast

how to insert no of rows effected by a sql stmt in bteq script into a table with in the same script

hi

can any one tell me how to insert the number of rows effected by a sql statement in a bteq script in to a table..

can i activitycount for this....

i want to insert the number of records inserted with in the same script...

thanks
mehaboob
2 REPLIES
Enthusiast

Re: how to insert no of rows effected by a sql stmt in bteq script into a table with in the same script

Did some research on this. Could not find out any way to do this in BTEQ.
activitycount indicates the actual number of rows affected by the request. But we can not use that in any SQL. It can just be used in "IF" statement to verify its value.

May be someone can suggest if we have any other alternative to solve this problem.
Enthusiast

Re: how to insert no of rows effected by a sql stmt in bteq script into a table with in the same script

Consulted with one TD expert i know. He also confirmed it can not be done using BTEQ.
However it can done using a stored procedure. Below is sample code if its useful to you.

create multiset table test (sno int) ;

insert test(1) ;
insert test(1) ;
insert test(2) ;
insert test(31) ;
insert test(14 );

replace procedure testproc (IN inval int, out msg varchar(100))
p1:
begin

update test
set sno = 100
where sno = :InVal;

if activity_Count > 0 then
set msg = 'number of records affected' || activity_count;
else
set msg = 'no records affected';
end if;

end;

BTEQ -- Enter your DBC/SQL request or BTEQ command:
call testproc (31, msg);

call testproc (31, msg);

*** Procedure has been executed.
*** Total elapsed time was 1 second.

msg
---------------------------------------------------------------------------
number of records affected 1.