How to check SP output value in Profiler

When stored procedure is called from client application, the call is typically made by using RPC call. So in order to capture SP traces from Profiler, typically RPC:Starting event and RPC:Completed event are checked and those settings are enough in most times. However, sometimes there are chances you want to check out output value of the stored procedure call. Especially where many concurrent calls occur in production server.

To capture the return value of SP, we need to enable RPC Output Parameter event.

1) Run Profiler.exe
2) Connect to SQL Server
3) In Trace Properties dialog, select TSQL_Replay template (or other appropriate for your purpose) in [Use this template].
4) Goto [Event Selection] tabe
5) Make sure [RPC Output Parameter] is selected.

6) Start trace.

The result of OUTPUT parameter will be shown in RPC:Completed event. When the row for RPC:Completed is selected in trace, the bottom pane show TextData and you will see output parameter value before SP call. In the example below, output @p3 is set to 562.