In stored procedures, how to find out the number of rows returned?

[CapsuleCorpJX]

I want to check if a select statement returned zero rows or not.

How do I check it?

Activity_Count I thought was used to only find out how many rows have been MODIFIED.

 

[RogerCruse]

Hi,
Try this -

select count(*) into :fred
from table_with_rows_perhaps

Then just check the value of :fred

Roger...

 

[CapsuleCorpJX]

Actually I'm trying to count the difference between two columns, I'm thinking about this solution:

select count(select benpkg
from :v2.PCM_PMBENGEN
MINUS
select SOURCE_SYSTEM_BNFT_PKG_ID
from :v1.BENEFIT_PACKAGE_ALL) INTO :htemp;

would it work?

 

[RogerCruse]

Hi,
You probably need to use DBC.SYSEXECSQL. Check the manual for a full explanation, but this would allow you the code something like this -

call dbc.sysexecsql('insert into yourtable
select count(*
from (select benpkg
from ' ||:v2.PCM_PMBENGEN
|| ' MINUS
select SOURCE_SYSTEM_BNFT_PKG_ID
from ' || :v1.BENEFIT_PACKAGE_ALL
|| ');');

(Assuming I haven't go a typo in the above), you should be use your variables in your SQL.

Roger...

 

[CapsuleCorpJX]

I'd rather stay away from dyn sql.
Will the following work?

select count(*)
INTO :htemp
from
(
<select statement 1>)
MINUS
<select statement 2>
);

 

[RogerCruse]

Hi,
I don't understand "I'd rather stay away from dyn sql"

It's probably the only way you'll get a stored procedure to do what you want.

Roger...

 

[dnoeth]

ACTIVITY_COUNT is set by *every* DML statement including SELECT.

If you want to check for a select returning no rows within a SP you can use a:

DECLARE CONTINUE HANDLER
FOR NOT FOUND
BEGIN
...
END

Dieter