CODEWITHSUNDEEP
sqlsql-server
WaseemSarwar
WaseemSarwar

Reputation: 77

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

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?

Upvotes: 4

Views: 4601

Answers (3)

Señor Cardgage
Señor Cardgage

Reputation: 93

If you look at the standard template in profiler you'll see that on the Events Selection tab under the Stored Procedures heading it only includes "RPC:Completed". The T_SQL_SPs template includes "SP:Completed", "SP_Starting" and "SP:StmtStarting". I believe you just need to include those in whatever template you choose.

Upvotes: 0

cristi71000
cristi71000

Reputation: 1124

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.

Upvotes: 4

Christine Penza
Christine Penza

Reputation: 474

It all depends on how you need to access and use the information, but it could be useful to log the values to a table. You could also try Debug in SSMS and set appropriate break points.

Upvotes: 0

Related Questions