Hi,

I'm trying to use GET DIAGNOSTICS to know if a select returns or not a row, in a function that contains a statement like this :

SET ( vVariable ) = ( select colunm
from table
fetch first 1 rows only );

GET DIAGNOSTICS vRows = ROW_COUNT;

But in this case always GET DIAGNOSTICS returns "0", someone knows what's happening ?

Thanks

The way I read the manual on GET DIAGNOSTICS is that it returns information about the previous SQL statement. It does not mention anything about subselects. If the statement is a INSERT, UPDATE, OR DELETE row_count returns the number of rows affected.

I think in your example, you statement is a SET, and no rows are affected by that.

HTH

Andy

But you know another way to get the number of rows without using GET DIAGNOSTICS, because i need to know if my select returns any rows.

I've tried to use SELECT INTO, and then use GET DIAGNOSTICS but i think the statement SELECT INTO doesn't work in a function.

I'm waiting for reply, thanks.

Why not use:
select count(*) from mytable where (same as other select stmt)

Andy