The forum is currently being migrated to a new host. While the migration happens, the old server is still available. Once the new machine is ready, I will copy the database to the new machine. Feel free to ask and answer questions in the meantime.

I have problem with the following code:

create procedure MyDummy()


select 'mydummy' from dummy;


create procedure MyDummyWrapper()


select * from MyDummy();


call MyDummy(); -->mydummy (As Expected)

call MyDummyWrapper();-->mydummy (As Expected)

alter procedure MyDummy()


select 'mydummy' from dummy where 1 = 0;


call MyDummy()-->empty (As Expected)

call MyDummyWrapper()-->mydummy (NOT As Expected!!!)

select * from MyDummyWrapper()-->empty (As Expected)

I have here 2 Questions:

  1. what is the difference between select and call? Why I have the problem only with call?
  2. how can I overcome the problem with call? is there any alternative for sp_recompile (as with Adaptive Server)?

asked 15 Sep '20, 05:18

Baron's gravatar image

accept rate: 41%

edited 15 Sep '20, 07:47

FWIW, it would be helpful to state more than just OK/NOK, I had expected the "NOK" would mean an error message and not a wrong result set. In my tests with and "call MyDummyWrapper()" returns the result "mydummy" whereas the other procedure invoocations return an empty result set as expected.

In my humble opinion this looks like a bug.

The behaviour can be fixed via

alter procedure MyDummyWrapper recompile;

Note, that in my tests both procedures (lacking a result set clause) are described via sysprocparm as having one result set column with type CHAR(7), which is appropriate.

select SP.proc_name, SPP.* from SYSPROCEDURE SP key join SYSPROCPARM SPP
where proc_name in ('MyDummy', 'MyDummyWrapper')

BTW, there's more to this:

If you drop the first procedure via "drop procedure MyDummy" and do not afterwards recomplie the second procedure, the second procedure still can be called successfully without an error, which is totally unexpected in my book.

So I do have to assume the wrapper procedure somehow interally caches the result set, also the attempt to disable plan caching via "set temporary option max_plans_cached = 0;" does not make a difference in my tests...

permanent link

answered 15 Sep '20, 07:41

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 15 Sep '20, 08:11

Yes, your fix works, I have tried it before! Moreover It also works if I restart the database (even without the mentioned fix).

The problem with the mentioned fix is, how can I know (not in this specific case, but generally) how many wrappers has the procedure MyDummy, and how many other procedures are affected of the last alter procedure MyDummy()

(15 Sep '20, 07:50) Baron
Replies hidden

As to "procedure dependencies": I do not know how to recognize those automatically, in contrast to the "View dependency feature".

(15 Sep '20, 08:10) Volker Barth

Yes, you have right, even after dropping the first procedure, then the wrapper is still functioning correct!

I don't think that this is related to caching the last result set, because after calling sa_flush_cache everything is still working as if nothing is happened!

(16 Sep '20, 03:15) Baron

Well, there are several types of caching, and I was relating to plan caching which has nothing to do with the database server's page caching that would be influenced by sa_flush_cache. But disabling plan caching did not matter here, either, as stated.

Furthermore, the noticed effect might be due to "inlining simple procedures", but again, that sould only happen for using procedures in the FROM clause, not for call statements, and this does not explain the effect, either.

(16 Sep '20, 03:56) 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 15 Sep '20, 05:18

question was seen: 196 times

last updated: 16 Sep '20, 03:56