SQL Server Profile - View Parameter Values?

Question

In SQL Server Profile, I have it tracing SP:StmtStarting events. The "TextData" includes information like the following:

EXEC MySortedProc 
   @param, NULL,
   @param2, NULL

What would I have to trace (or can I?) to view the value of those parameters?

Answer 1

If I get you correctly, you have a stored procedure being called by another stored procedure and you want to get the values of your parameters that are being passed to the inner stored procedure?

I don't think it's possible with SQL Profiler. If I find a way though, I'll post an update. If this is just for debugging then you can save the values into a table to check after the fact, but that's not a very good production solution unless you really want that kind of extensive logging.

Answer 2

For some evensts, SQL Profiler will show NULL values instead of real datain TextData column. You could a metod that is explained below article to capture this info. I used this method to capture another set of queries that were throwing sort warnings. Let me know if this helps.

http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!367.entry

Answer 3

Somewhat scared to have misunderstood the question, but you could profile on the RPC:Completed event which will return the result for stored procedure execution in the textdata column like:

exec usp_yourproc @param = 'value'

Answer 4

Thank you, Tone! I've been looking for ages for a way to trace calls to SQL Server and see what parameters they're executing with. The RPC:Completed event is exactly what I needed! - Betsy