Nested CREATE PROCEDURE

[Baron]

Is it possible to create procedure within another procedure?

create procedure my_creator()
begin
  select * from dummy;
  create procedure my_procedure()
  begin
    select * from dummy;
  end
end

This block is executable and the procedure 'my_creator' is created. When I call this procedue 'my_creator', then the other procedure my_procedure is NOT created.

Could maybe anybody help?

Thanks in advance

[reimer_pods]

Which version are you using? In 12.0.1 both procedures get created. Checked with

select * from sys.sysprocedure where proc_name like 'my%'

BTW what are you trying to achieve with this construction?

[Baron]

The statement 'select * from dummy' has cuased the problem!!! it has worked on Version 10.0.1 too after removing this statement:

create procedure my_creator() begin create procedure my_procedure() begin select * from dummy; end end

[Baron]

It is so, in case the body of any procedure contains a select statement, then the execution of the procedure will be ended once reaching this statement, so this select statement will be the last executed statement within the procedure without throwing any error. Moreover, if this procedure (which contains a select statement) is called within another procedure then the execution of this caller procedure will be stopped as well. Maybe this serves some logic or some intention!!

[VolkerBarth]

I don't think so. If the procedure does a SELECT, the caller has to consume the according result set, so the procedure can continue its work. Say in dbisqlc, you would call RESUME to continue execution...

See that answer for a detailed description...