Dynamically Retrieve Parameter Names & Current Val

问题:

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

回答1:

You don't have to dynamically retrieve names and parameters inside the stored proc, because of once the stored proc is created or altered it cannot change its parameters until it is altered or recreated again.

Instead, you may have the list of parameters inside the stored proc static, but to not enum params manually you can have it generated dynamically by DDL trigger.

Define some comment-tags, that will be used to mark the place, where the parameters should be listed within the stored proc and add them to the body of the stored proc, where appropriate. The trigger should find markers and alter proc inserting static list of parameter names and their values between the markers. The example follows.

DDL-trigger

create trigger StoredProc_ListParams on database
for CREATE_PROCEDURE, ALTER_PROCEDURE
as
begin
    set nocount on;

    if @@nestlevel > 1
        return;

    declare @evt xml, @sch sysname, @obj sysname, @text nvarchar(max);

    set @evt = eventdata();
    set @text = @evt.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]', 'nvarchar(max)');

    if @text is NULL -- skip encrypted
        return;

    set @sch = @evt.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'sysname');
    set @obj = @evt.value('(/EVENT_INSTANCE/ObjectNa1me/text())[1]', 'sysname');

    declare @listParams nvarchar(max);

    set @listParams = '
    select name, value
    from (values ' + stuff(
        (select ',
        ' + '(' + cast(p.parameter_id as varchar(10)) + ', ''' + p.name + '''' +
        ', cast(' + p.name + ' as sql_variant))'
    from sys.parameters p
        join sys.objects o on o.object_id = p.object_id and o.type = 'P'
        join sys.schemas s on s.schema_id = o.schema_id
    where s.name = @sch and o.name = @obj
    order by p.parameter_id
    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 1, '') + '
        ) p(num, name, value)
    order by num';

    declare @startMarker nvarchar(100), @endMarker nvarchar(100);
    set @startMarker = '--%%LIST_PARAMS_START%%';
    set @endMarker = '--%%LIST_PARAMS_END%%';

    if left(@text, 6) = 'create'
        set @text = stuff(@text, 1, 6, 'alter');

    declare @ixStart int, @ixEnd int;
    set @ixStart = nullif(charindex(@startMarker, @text), 0) + len(@startMarker);
    set @ixEnd = nullif(charindex(@endMarker, @text), 0);

    if @ixStart is NULL or @ixEnd is NULL
        return;

    set @text = stuff(@text, @ixStart, @ixEnd - @ixStart, @listParams + char(13) + char(10));

    if @text is NULL
        return;

    exec(@text);
end

The script of the stored proc for the test:

create procedure dbo.TestProc
(
    @id int,
    @name varchar(20),
    @someFlag bit,
    @someDate datetime
)
as
begin
    set nocount on;

--%%LIST_PARAMS_START%%
    -- list params for me here, please
--%%LIST_PARAMS_END%%

end

And the following is how the stored proc actually looks in the database once the above create script is executed:

alter procedure [dbo].[Test]
(
    @id int,
    @name varchar(20),
    @someFlag bit,
    @someDate datetime
)
as
begin
    set nocount on;

--%%LIST_PARAMS_START%%
    select name, value
    from (values 
        (1, '@id', cast(@id as sql_variant)),
        (2, '@name', cast(@name as sql_variant)),
        (3, '@someFlag', cast(@someFlag as sql_variant)),
        (4, '@someDate', cast(@someDate as sql_variant))
        ) p(num, name, value)
    order by num
--%%LIST_PARAMS_END%%

end

The one restriction with this approach is that it will not work with encrypted stored procs. Also you will have to do some adjustments, if you wish to handle parameters of the table type.

回答2:

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