如何在存储过程执行时自动获取参数名称与对应值

需求说明 #

需要在存储过程执行期间，自动获取自身的所有参数名称及对应实际传入值。已知通过[sys].[all_parameters]结合存储过程ID可获取参数名称，但无法访问管理员级别的[sys].[dm_exec_input_buffer]视图，之前尝试的游标动态取值方案无法正常运行，要求实现通用方案：无需硬编码参数名称，仅通过@@PROCID适配任意存储过程。

可行实现方案 #

核心思路是利用系统视图获取当前存储过程的参数元数据，动态构造SQL语句，并通过sp_executesql将当前存储过程的参数传递给动态SQL，从而获取参数的实际值。

代码示例（SQL Server 2017+） #

CREATE PROCEDURE [dbo].[get_proc_params_demo]
(
    @number1 int,
    @string1 varchar(50),
    @calendar datetime,
    @number2 int,
    @string2 nvarchar(max)
)
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取当前存储过程的参数元数据：名称、类型，按定义顺序排列
    DECLARE @paramList NVARCHAR(MAX);
    DECLARE @paramDeclare NVARCHAR(MAX);
    DECLARE @paramSelect NVARCHAR(MAX);

    SELECT 
        @paramList = STRING_AGG(QUOTENAME([name]), ', ') WITHIN GROUP (ORDER BY parameter_id),
        @paramDeclare = STRING_AGG(N'@' + [name] + ' ' + SYSTEM_TYPE_NAME, ', '),
        @paramSelect = STRING_AGG(N'CAST(' + QUOTENAME([name]) + ' AS NVARCHAR(MAX)) AS ' + QUOTENAME([name]), ', ')
    FROM [sys].[all_parameters]
    WHERE object_id = @@PROCID;

    -- 构造动态查询语句
    DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + @paramSelect;

    -- 执行动态SQL，传入当前存储过程的所有参数
    EXEC sp_executesql 
        @stmt = @sql,
        @params = N'' + @paramDeclare + N'',
        @number1 = @number1,
        @string1 = @string1,
        @calendar = @calendar,
        @number2 = @number2,
        @string2 = @string2;
END
GO

-- 测试执行
EXEC [dbo].[get_proc_params_demo]
    @number1=42, 
    @string1='is the answer', 
    @calendar='2019-06-19',
    @number2=123456789,
    @string2='another string'

兼容SQL Server 2016及更早版本的代码 #

如果使用的是SQL Server 2016或更早版本（不支持STRING_AGG），可以替换参数拼接逻辑为FOR XML PATH方式：

CREATE PROCEDURE [dbo].[get_proc_params_demo]
(
    @number1 int,
    @string1 varchar(50),
    @calendar datetime,
    @number2 int,
    @string2 nvarchar(max)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @paramList NVARCHAR(MAX);
    DECLARE @paramDeclare NVARCHAR(MAX);
    DECLARE @paramSelect NVARCHAR(MAX);

    -- 用FOR XML PATH拼接参数列表、声明和查询字段
    SELECT 
        @paramList = STUFF((SELECT ', ' + QUOTENAME([name])
                            FROM [sys].[all_parameters]
                            WHERE object_id = @@PROCID
                            ORDER BY parameter_id
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''),
        @paramDeclare = STUFF((SELECT ', @' + [name] + ' ' + SYSTEM_TYPE_NAME
                               FROM [sys].[all_parameters]
                               WHERE object_id = @@PROCID
                               ORDER BY parameter_id
                               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''),
        @paramSelect = STUFF((SELECT ', CAST(' + QUOTENAME([name]) + ' AS NVARCHAR(MAX)) AS ' + QUOTENAME([name])
                              FROM [sys].[all_parameters]
                              WHERE object_id = @@PROCID
                              ORDER BY parameter_id
                              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

    DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + @paramSelect;

    EXEC sp_executesql 
        @stmt = @sql,
        @params = N'' + @paramDeclare + N'',
        @number1 = @number1,
        @string1 = @string1,
        @calendar = @calendar,
        @number2 = @number2,
        @string2 = @string2;
END
GO

方案说明 #

• 通用性：通过@@PROCID直接获取当前存储过程的ID，无需硬编码存储过程名称，将这段逻辑嵌入任意存储过程即可生效。
• 权限友好：仅使用普通权限即可访问的[sys].[all_parameters]视图，无需管理员权限。
• 类型兼容：通过CAST将所有参数转为NVARCHAR(MAX)统一输出，避免不同数据类型的显示问题，也可根据需求调整转换逻辑。
• 顺序一致性：按parameter_id排序参数，保证输出顺序与存储过程定义的参数顺序一致。

内容的提问来源于stack exchange，提问作者Hugo