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

asked 04 Jan, 03:28

Sarkis's gravatar image

Sarkis
24561629
accept rate: 0%

edited 04 Jan, 03:46

Reimer%20Pods's gravatar image

Reimer Pods
4.3k354786

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?

(04 Jan, 03:48) Reimer Pods

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

(04 Jan, 04:23) Sarkis
Comment Text Removed

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!!

permanent link

answered 04 Jan, 08:26

Sarkis's gravatar image

Sarkis
24561629
accept rate: 0%

edited 04 Jan, 08:27

1

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...

(04 Jan, 11:22) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27

question asked: 04 Jan, 03:28

question was seen: 63 times

last updated: 04 Jan, 11:36