j d imported from SE
sql-server sql-server-2016
Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored procedure.)

I know I can see the queries of the stored procedure that are currently being executed using the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs but they don't show the values of the parameters being used.
Top Answer
Martin Smith
For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing `query_thread_profile` (and be on [at least  SQL Server 2016 SP1][1]) but then can use

       (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    select query_plan.query('//ParameterList')
    from sys.dm_exec_query_statistics_xml(@session_id)

and see the `ParameterRuntimeValue` and `ParameterCompiledValue` from the execution plan of the statement being executed. 

For SQL Server 2019+ the infrastructure is enabled by default so the above will just work. 

This might not be all the parameters of the stored proc however as it will only include params used by the execution plan.

  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-statistics-xml-transact-sql?view=sql-server-ver15
How to get the parameter values of a stored procedure that's in the middle of execution?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.