Question Re: create procedure without defined result in Technology Q&A

create procedure without defined result

Baron — Thu, 03 Oct 2019 11:32:39 GMT

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?

Re: create procedure without defined result

VolkerBarth — Thu, 03 Oct 2019 14:42:03 GMT

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.

Re: create procedure without defined result

VolkerBarth — Thu, 03 Oct 2019 14:44:19 GMT

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

Re: create procedure without defined result

Baron — Fri, 04 Oct 2019 04:18:12 GMT

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

Re: create procedure without defined result

Breck_Carter — Fri, 04 Oct 2019 08:40:15 GMT

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