CODEWITHSUNDEEP
sqlstored-proceduresdb2
Abruzzo Forte e Gentile
Abruzzo Forte e Gentile

Reputation: 14869

db2 doesn't allow to use function in a stored procedure

I have the following error

   An unexpected token "table" was found following "elect .."

That is produced after the code below. If I call the function it works fine

   --example
   select * from foo('2013-02-20')

but when I plug it into a stored proceddure it deosnt' work. Is it allowed to call in a function like the one I did below?

   create function foo( my_date date ) returns table(data integer) 
   language sql READS SQL DATA  return select id,sum(value) from table where date=my_date group by id; 

   create procedure list_open_positions(my_date date) 
   LANGUAGE SQL begin select * from table(foo(my_date)); end;

Upvotes: 1

Views: 2155

Answers (1)

user1919238
user1919238

Reputation:

Stored procedures can't contain a bare select statement, as they don't output the results of queries directly.

The table function should work fine in a select statement that is part of an expression. For example:

create procedure bar(my_date date)
language sql
begin
    insert into some_table
         select * from table(foo(my_date));
end

This answer is not 100% definitive without knowing the version and platform of DB2 you are using. There are major differences between versions in what is allowed in functions/procedures. An example such as above works fine in 9.7 for LUW.

Upvotes: 2

Related Questions