SQL profiler not showing incoming parameters of store procedure

Question

My Task

When user updates some values on the front end UI of an application, I want to know which SQL table/fields are updated, which SQL statements were executed, etc.

My method

Using SQL profiler, I updated some value from the applications user interface. then try to track the SQL statements executed. In Events Selection Tab, I selected all RPC & TSQL.

Problem

SQL Profiler does not show the value of incoming parameter, showing

e.g.

exec [sys].sp_describe_undeclared_parameters N'SELECT FIELD_NAME FROM EXTRA_FIELDS WHERE TABLE_NAME = @P1 AND EDITABLE = ''N'' ORDER BY CONTROL_NO, SEQNO'

Is there anyway I can get the @P1 value?

See screenshot

Answer 1

This is the default feature of profiler unless you enable below by going into options of profiler capturing:

1. RPC:Completed

2. SP:StmtCompleted

3. SQL:BatchCompleted

4. SP:stmtstarting

5. SQL:stmtstarting

6. SQL:stmtcompleted

However please note that if there is nested call of procedure inside another procedure, it may not get captured.

Please go through the below set of Knowledge base concerning raised question:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c7aa2cab-f8a4-44bc-bbb5-8ef54fcd9f59/tracking-input-output-parameters-of-a-stored-proc-in-sql-server-profiler-trace?forum=sqlsearch

https://www.mytecbits.com/microsoft/sql-server/capturing-stored-procedures-parameter-values

https://www.sqlservercentral.com/forums/topic/is-there-any-way-to-capture-parameter-values-passed-for-nested-stored-procedures

You may use free scripts provided by Brent Ozar sp_BlitzCache wherein you may use cached parameter column to see the parameter of a procedure and this is not going to put any load on the server.

You may also enable extended event to capture the events instead of using profiler, which is again not going to bother your server.

Hope this helps.