create procedure without defined result

[Baron]

I know that it is possible to create a procedure without defining the result of it, but this is true only if I dont execute the select statement within an execute immediate.

create procedure myproc()

begin

select 'ss' from dummy; -- this works

execute immediate ('select ''ss'' from dummy'); -- this does not work

end;

Any Ideas?

[VolkerBarth · Accepted solution]

Does it work when you add the WITH RESULT SET ON clause, see here...? By default, EXECUTE IMMEDIATE is not supposed to return a result set.

[VolkerBarth · Accepted solution]

Besides that, there may be ways to parameterize your statement that do not require dynamic SQL, say by using connection-specific variables...

[Baron · Accepted solution]

Thank you very much! it has worked with (WITH RESULT SET ON)

[Breck_Carter · Accepted solution]

...so don't forget to check-mark the answer 🙂