sql server - Dynamically Retrieve Parameter Names & Current Values Inside T-SQL Stored Procedure

Question

I have hundreds of templated ETL stored procedures for Business Intelligence. They log their operational activity to audit table. The one thing missing is logging the parameter information being passed into them. The problem is the parameters aren't always the same from one SP to another. I am looking for a standard piece of code that i can stick into the procedure that can loop through all parameters for the proc and retrieve the current values passed in. I plan on mashing that together in a string to also log to the table. Any ideas?

Thank you in advance for any pointers! - Tim

Answer 1

I am looking for a standard piece of code that i can stick into the procedure that can loop through all parameters for the proc and retrieve the current values passed in--

You can get all values passed in for an sp using below query

Example :
I have below stored proc which gives me sales details (for demo only)

alter  proc dbo.getsales
(
@salesid int
)
as
begin
select 
* from sales where cust_id=@salesid
end

I have called my sp like below..

exec  dbo.getsales 4

Now if i want to get value passed ,i can use below query

select top 10* from sys.dm_exec_cached_plans cp
cross apply
sys.dm_exec_text_query_plan(cp.plan_handle,default,default)
where objtype='proc'

which showed me below as the compile time value

with that said,there are many things to consider..we can use xml methods to get this value

now what happens, if i run the same stored proc again for value of 2 ..

<ColumnReference Column="@salesid" ParameterCompiledValue="(4)" ParameterRuntimeValue="(2)" />

One important catch here, is the above values are shown when i selected execution plan to show from ssms.

But what will be the value in cache,lets see it using above plan cache query again

<ColumnReference Column="@salesid" ParameterCompiledValue="(4)"/>

It is still showing compiled value ,plus usecounts column as 5--`which means this plan has been used 5 times and parameter that was passed when the plan was initially compiled is 4.which also means ,run time values are not stored in cached plans details..

So in summary,you can get runtime values passed to stored proc

• 1.Values which are passed while statement is compiled(
  You can start gathering this info over period of time and log them against stored proc,I think over time with server reboots,plan recompilations you can get new set of parameter values )
• 2.Getting in touch with DEV team is also good way,since they can give you total list of parameters that can be passed ,if this excercise is cubersome