Shawn,

The following articles may be helfull to you:
- INF: Troubleshooting Stored Procedure Recompilation mskb #243586
- HOW TO: Identify the Cause of Recompilation in an SP:Recompile Event mskb #308737


>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