Hi All,
I have come across a situation where a master stored procedure calls several procedures, and with in those procedures will call other procedures.

I.E.
Master -- calls Child
Child calls to many Sub-Childs

1) If sub child compiles does child an master have to recompile?

2) Is there a way to stop SQL server from compiling every time it sees the procedure?

3) Or typically does this compile only happen once?

4) By adding nocompile to the top of the procedure any performance issues involved?

This process is very large in logic. And will be used by many callers, they will utilize this process day in and day out.

Thanks
Shawn