动态检索参数名称T-SQL 存储过程中的当前值 [英] Dynamically Retrieve Parameter Names & Current Values Inside T-SQL Stored Procedure

问题描述

我有数百个用于商业智能的模板化 ETL 存储过程.他们将他们的操作活动记录到审计表中.缺少的一件事是记录传递给它们的参数信息.问题是从一个 SP 到另一个 SP 的参数并不总是相同的.我正在寻找一段标准代码，我可以将其粘贴到可以循环遍历 proc 的所有参数并检索传入的当前值的过程中.我计划将它们混合在一个字符串中，以便也记录到表中.有什么想法吗?

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

推荐答案

您不必动态在存储过程中检索名称和参数，因为一旦存储过程被创建或改变，它不能改变它的参数，直到它被改变或重新创建.

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.

相反，您可以在存储的 proc static 中拥有参数列表，但为了不手动枚举参数，您可以通过 DDL 触发器动态生成它.

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.

定义一些注释标签，用于标记参数应在存储过程中列出的位置，并在适当的情况下将它们添加到存储过程的主体中.触发器应该找到标记并改变 proc 在标记之间插入参数名称及其值的静态列表.示例如下.

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 触发器

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.

这篇关于动态检索参数名称T-SQL 存储过程中的当前值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！