UDF with dynamic SQL and result

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fred BROUARD - SQLpro - 05 Feb 2005 11:25 GMT

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

Reply to this Message

Serge Rielau - 05 Feb 2005 17:46 GMT

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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message