Executing UDF from SQL Stored Procedure

[Antanas]

Hi. I want to execute FUNC1 inside my stored Procedure PROC1.

CREATE PROCEDURE PROC1()
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
DECLARE M VARCHAR(100);
SET M= 'VALUES FUNC1()';
EXECUTE IMMEDIATE M;
END

I get error:

SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement. SQLSTATE=42612

How can make it get executed?

[Knut Stolze]

Antanas wrote:

Hi. I want to execute FUNC1 inside my stored Procedure PROC1.

CREATE PROCEDURE PROC1()
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
DECLARE M VARCHAR(100);
SET M= 'VALUES FUNC1()';
EXECUTE IMMEDIATE M;
END

I get error:

SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement. SQLSTATE=42612

How can make it get executed?

As the error message said, you cannot run a query through EXECUTE IMMEDIATE.
You can either:
(1) use PREPARE + EXECUTE + OPEN (CURSOR) + FETCH + CLOSE
(2) call the function directly (you're in SQL)

The first approach is necessary if you do need dynamic SQL in case you build
your query on the fly. If the query/function call doesn't change, stick
with (2):

BEGIN
DECLARE M VARCHAR(100);
SET M = FUNC1();
END
--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Antanas]

Thanks Knut! Second approach works fine with me.