Capturing Stored Procedure’s Parameter Values

Once I had to find the different values passed to a stored procedure as parameter executed from different applications on production environment. Though there are several ways to find the parameter values, I used the SQL Server Profiler trace to find them. Here are the steps I have followed to capture the stored procedure’s parameter values using profiler.

Capturing Stored Procedure’s Parameter Values Using Profiler

1. From the profiler trace window, go to menu Files > Properties.
2. In the Trace Properties window, go to Events Selections tab.
3. From the left Events panel, expand the node Stored Procedures and select one or both of the below events as fits to you:
   1. RPC:Completed: Use this event if you want to capture the stored procedures executed from applications alone. This event will not capture the procedures executed form a batch or from inside another procedure or from SQL Server client utilities like SSMS, MSSQL-CLI, etc…
   2. SP:Completed: Use this even if you want to capture the procedure executed from inside another stored procedure or from a batch or from applications. If you are not sure where the procedure is executed from, then use both SP:Completed and RPC:Starting.
4. (Optional) If you want to capture only a specific stored procedure then:
   1. Select the check box Show all columns.
   2. Press the Columns Filters… button.
   3. In the Filter pop-up window, from the left panel select Object Name.
   4. From the right panel, under Like tree node, enter the stored procedure name and press OK.
5. Press Run button to start the trace. Now the trace window is ready and starts to capture the stored procedure events.
6. Once the trace window captures the stored procedure, check the TextDate column, you can see the stored procedure along with the parameter values.

Sample Procedure Captured In Trace

Drawbacks of Profiler Method

If the stored procedure is called from inside another stored procedure, the events RPC:Completed or SP:starting can not trap the values if they are passed through variables. In such case you may need to think about having a logging system to log (insert) the parameter values to a table.

Related Articles

• Tips about capturing only the stored procedures using SQL Server profiler executed against a database.
• Filtering the SQL Server profiler trace to capture only the events from a specific database.