SQL Guide : Diagnostics and troubleshooting for SQL : Tracing facilities for stored procedures and triggers
  
Tracing facilities for stored procedures and triggers
When debugging a stored procedure or a trigger, you may want to add "trace" commands to see which parts of the code are executing. Or you may want to trace every statement within the procedure or trigger. The following two sections explain how to do these things.
See also
User-definable trace output from procedure code
Procedure execution trace
Diagnostics and troubleshooting for SQL