Unanswered: creating scalar UDF which modifies data
I am trying to create a function which returns a scalar data. My function tries to insert a row in a table as well.
CREATE function getManufacturerId
modifies sql data
declare manufacturer_row bigint;
set manufacturer_row = (select manufacturer_id from manufacturer where manufacturer_name = manufacturer);
IF(manufacturer_row IS NULL ) THEN
set manufacturer_row = nextval for manufacturer_seq ;
INSERT INTO manufacturer VALUES(manufacturer_row, manufacturer);
I am getting following error.
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613
After googling for a while i found out that only table functions can declare "modifies sql data". This raises the question whether i can create a table function to get only one value from the function and use the return value in "call procedure statement".
In my procedure i have to use the above function
And the above statement will return me the result set if i use table function, how can i use that result set in procedure to get value.
Similarly instead of using function which has plenty of restrictions, if i create procedure can i get get one value by calling a procedure from a procedure.
I hope I am able to explain my problem, if not please inform me i will try to explain again.
Any hint to solve above problem will be highly appreciated.