Hi,

unfortunately I have no current DB2 server to try this code :

CREATE FUNCTION FN_HOW_MANY_ROWS (TableName VARCHAR(128))
RETURNS INTEGER
LANGUAGE SQL
PARAMETER STYLE SQL
SPECIFIC HowManyRows
DETERMINISTIC
RETURNS NULL ON NULL INPUT
READS SQL DATA
BEGIN
DECLARE SQL_string VARCHAR(1000);
SET SQL_string = 'SELECT COUNT(*) FROM ' || TableName;
RETURN EXECUTE IMMEDIATE SQL_string;
END

Is this possible, or if it is not the right way to di it, how can I have a UDF
that return the number of rows of a table wich the name is a parameter ?

Thanks.

Fred

Couple of changes:
1. In DB2 V8.2 put the code into a procedure then
create a function that CALLs the procedure
2. SET statement is not dynamic.
You need to use a cursor ofer one row.

CREATE PROCEDURE p(..) ..do fancy stuff
CREATE FUNCTION foo(..)
...
BEGIN ATOMIC
DECLARE ret ..;
CALL p(..., ret);
RETURN ret;
END

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab