Sorry for long post, but....

When you call a stored procedure - is this procedure actually compiled each time it is called? (ie, before the procedure is executed, it actually compiles each time?)

I am asking due to a practice exam question I got concerning a local procedure calling a remote procedure and this remote procedure was compiled AFTER the calling local procedure. So, I thought that since the local procedure depends on this remote procedure - the local procedure would then invalidate due to the timestamp.

But, the explanation to a question as to what happens when the local procedure is called (and thus calls this remote procedure with the later timestamp) was the

procedure will invalidate, but that executing it a second time will successfully validate the remote procedure.

This sounds like to me that when the remote procedure was called the first time - it was actually being recompiled, so the timestamp was updated and making it valid. Is this a true statement??? I can't seem to find any resource the really explains this in detail.

Can someone please clarify? Thanks!!

Last edited by odonata on 03-27-02 at 12:12 AM

Report this post to a moderator

as far as I understand it a stored procedure is recompiled each time it is called. I'm not 100% sure either but my best guess is that it is.

Report this post to a moderator

Thanks Ian_J

Judging by the explanations to some of these questions - that is what is being implied, but I am having troubles deciphering what is explained sometimes.

I was hoping to see an explanation stating "each time a procedure is called - it is compiled to ensure the procedure is valid" or something along those lines, but they always seem to dance around from actually saying that!

Report this post to a moderator

well as far as I understand it, that is why you can pin commonly used procedures so that they are cached and do not have to be compiled again. I might be mistaken though and it might just be that they don't have to be re-read from disk again but do have to be re-compiled

Report this post to a moderator

ok, I got a pretty good explanation of what's going on, here it is:


When compiling a local construct that invokes a remote construct, the compilation timestamp of the remote construct is stored in the object code of the local construct. When executing the local construct, the compilation timestamp of the remote procedure recorded in its object code will be compared to the actual compilation timestamp. If the actual timestamp of the remote construct is later than what is recorded in the local construct's object code, an error will result. This will mark the local construct invalid. Executing it a second time will result in a successful recompilation becase the new timestamp of the remote construct will be recorded in the object code of the local construct.

enjoy!

Report this post to a moderator