How to create a function that does a dynamic SQL call?

Hi, I am trying to create a function that calls some dynamic SQL and then returns some value, like the example one below. I am using DB2 v9 on Windows.

CREATE FUNCTION halloWorld2()
     RETURNS INTEGER
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     READS SQL DATA
     BEGIN ATOMIC
     EXECUTE IMMEDIATE 'SOME DYNAMIC SQL HERE';
     RETURN 1;
   END@

However, I am getting some strange error message. Could someone please help me. I have no clue what I am doing wrong here. The error message is:
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "EXECUTE IMMEDIATE" was found following "A      
BEGIN ATOMIC ".  Expected tokens may include:  "<compound_return>".  LINE
NUMBER=6.  SQLSTATE=42601

SQL0104N  An unexpected token "EXECUTE IMMEDIATE" was found following "A
     BEGIN ATOMIC
".  Expected tokens may include:  "<compound_return>              ".
mersisAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mersis,

I'm not aware that you can use EXECUTE IMMEDIATE in a function.  (If anyone knows differently, please tell me!)  A function is a "compiled" bit of code, and dynamic SQL is evaluated at execution time, not compile time.

EXECUTE may not be any better because it's tied to the PREPARE statement.  (EXECUTE IMMEDIATE is really just PREPARE and EXECUTE run as a single command.)

I believe that CALL statements are fine.


Kent


0
 
mersisAuthor Commented:
So it works like that:
CREATE FUNCTION halloWorld2()
     RETURNS INTEGER
     DETERMINISTIC NO EXTERNAL ACTION
     READS SQL DATA
     BEGIN ATOMIC
     --EXECUTE IMMEDIATE 'SOME DYNAMIC SQL HERE'
     RETURN 1;
   END@

But as soon as I uncomment the EXECUTE line I start getting the error message above.
0
 
ghp7000Commented:
some dynamic sql statment canno tbe insert update or delete-is this what you are doing?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mersisAuthor Commented:
no, I am just trying to compile the function first, but DB2 does not allow me to. If I convert it into STORED PROC it works fine, but as a function it refuses to compile the very same code.

Any ideas why? Can I have EXECUTE or CALL statements in an SQL FUNCTION definition?
0
 
ghp7000Commented:
yes, kdo is right, I am getting confused between sp and function :)
0
 
momi_sabagCommented:
hi
it does not say anywhere that one can not use execute immediate in a function,
by looking here it is also implied that execute immediate can be used
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0007249.htm
0
 
mersisAuthor Commented:
Hi guys,
thanks for your replies.
after fighting the whole day with DB2 today I come to the following conclusion:
1) PREPARE/EXECUTE is *NOT* allowed inside a FUNCTION body. There is a list of statements that are allowed and PREPARE/EXECUTE are simply not there. So whenever you try using those it causes a syntax error.
2) As always with DB2, there is a complicated way around it (*sigh*, I must say I am disgusted by this DB system). Since PREPARE/EXECUTE is allowed inside a STORED PROCEDURE, and CALL statements are allowed inside FUNCTIONs, you can create a stored proc that issues EXECUTE statements, and then call that stored proc from a function, passing a string that has to be executed to it.
Hope this discovery will eventually help someone...
0
 
momi_sabagCommented:
can you point me to the manual where you saw the list of statements that are allowed within a function body and execute/prepare was not there ?
i looked for it but couldnt find it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.