Can we get a stored procedure call using Sql server profiler within another SP

Question

I have a stored procedure named CreateUpdateNewOrder and i call another SP in it named CreateClinicalDocument Now i want to see what exact values my second SP is getting for execution. I can run a sql profiler tool to see what input values CreateUpdateNewOrder is getting but I can't think of any other way of getting input values for inner SP call other than print them in query. Anyone has better way to do it?

Answer 1

You can run SQL Profiler and select the SPS template instead of the default one.This will show you every statement executed, even if it's inside a stored procedure.To use the SPS template you need to do the following:

1. File -> New trace
2. In the dialog that opens go to combo "Use this template" and select TSQL_SPs.
3. Now continue setting up your profiling session as you would normally.

Once you start the trace you will notice it's much more verbose. It will break down each procedure and will show what's executed line by line.Please let me know if you would need any other details.