如何从存储过程动态捕获参数值？需可复用Catch块代码

实现思路 #

借助SQL Server内置的@@PROCID获取当前存储过程ID，通过系统视图sys.parameters动态拉取参数列表，再用动态SQL逐个获取参数的实际传入值，最终拼接成类JSON格式的字符串，这段代码可以直接复制到任意存储过程的Catch块中复用。

可复用的Catch块代码片段 #

BEGIN CATCH
    DECLARE @ParamJSON NVARCHAR(MAX) = N'{'
    DECLARE @ParamName NVARCHAR(128), @DataType NVARCHAR(128)

    -- 游标遍历当前存储过程的所有参数
    DECLARE param_cursor CURSOR FAST_FORWARD FOR
        SELECT 
            name AS ParamName,
            TYPE_NAME(user_type_id) AS DataType
        FROM sys.parameters
        WHERE object_id = @@PROCID
        ORDER BY parameter_id

    OPEN param_cursor
    FETCH NEXT FROM param_cursor INTO @ParamName, @DataType

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @ParamValue NVARCHAR(MAX)
        -- 动态SQL获取参数值，处理不同数据类型的格式
        EXEC sp_executesql 
            N'SELECT @Value = CASE 
                WHEN ''' + @DataType + ''' IN (''varchar'', ''nvarchar'', ''char'', ''nchar'', ''text'', ''ntext'') THEN QUOTENAME(CAST(@' + @ParamName + ' AS NVARCHAR(MAX)), ''"'')
                WHEN ''' + @DataType + ''' IN (''datetime'', ''datetime2'', ''smalldatetime'', ''date'', ''time'') THEN QUOTENAME(CAST(@' + @ParamName + ' AS NVARCHAR(MAX)), ''"'')
                ELSE CAST(@' + @ParamName + ' AS NVARCHAR(MAX))
              END',
            N'@Value NVARCHAR(MAX) OUTPUT',
            @Value = @ParamValue OUTPUT

        -- 拼接JSON键值对
        SET @ParamJSON += N'"' + @ParamName + '": ' + ISNULL(@ParamValue, 'null') + ','

        FETCH NEXT FROM param_cursor INTO @ParamName, @DataType
    END

    CLOSE param_cursor
    DEALLOCATE param_cursor

    -- 去除最后一个逗号并闭合JSON
    IF RIGHT(@ParamJSON, 1) = ','
        SET @ParamJSON = LEFT(@ParamJSON, LEN(@ParamJSON) - 1)
    SET @ParamJSON += N'}'

    -- 可根据需求将JSON写入日志表，或通过RAISERROR输出给调用方
    RAISERROR(N'存储过程执行错误，传入参数: %s', 16, 1, @ParamJSON)
END CATCH

代码说明 #

• @@PROCID：自动获取当前执行的存储过程ID，确保只拉取当前存储过程的参数列表。
• sys.parameters：系统视图，存储了数据库内所有存储过程、函数的参数元数据。
• 游标遍历：逐个处理每个参数，针对字符串、日期类型添加双引号，数值类型直接输出，保证JSON格式的合法性。
• sp_executesql：安全执行动态SQL获取参数值，避免注入风险，同时支持输出参数传递值。

完整示例存储过程 #

CREATE PROCEDURE dbo.ExampleProcedure
    @ID INT,
    @UserName NVARCHAR(50),
    @CreateDate DATETIME,
    @IsActive BIT
AS
BEGIN
    SET NOCOUNT ON;

    -- 模拟错误触发
    BEGIN TRY
        SELECT 1/0; -- 故意制造除零错误
    END TRY
    BEGIN CATCH
        -- 粘贴上述可复用Catch代码
        DECLARE @ParamJSON NVARCHAR(MAX) = N'{'
        DECLARE @ParamName NVARCHAR(128), @DataType NVARCHAR(128)

        DECLARE param_cursor CURSOR FAST_FORWARD FOR
            SELECT 
                name AS ParamName,
                TYPE_NAME(user_type_id) AS DataType
            FROM sys.parameters
            WHERE object_id = @@PROCID
            ORDER BY parameter_id

        OPEN param_cursor
        FETCH NEXT FROM param_cursor INTO @ParamName, @DataType

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @ParamValue NVARCHAR(MAX)
            EXEC sp_executesql 
                N'SELECT @Value = CASE 
                    WHEN ''' + @DataType + ''' IN (''varchar'', ''nvarchar'', ''char'', ''nchar'', ''text'', ''ntext'') THEN QUOTENAME(CAST(@' + @ParamName + ' AS NVARCHAR(MAX)), ''"'')
                    WHEN ''' + @DataType + ''' IN (''datetime'', ''datetime2'', ''smalldatetime'', ''date'', ''time'') THEN QUOTENAME(CAST(@' + @ParamName + ' AS NVARCHAR(MAX)), ''"'')
                    ELSE CAST(@' + @ParamName + ' AS NVARCHAR(MAX))
                  END',
                N'@Value NVARCHAR(MAX) OUTPUT',
                @Value = @ParamValue OUTPUT

            SET @ParamJSON += N'"' + @ParamName + '": ' + ISNULL(@ParamValue, 'null') + ','

            FETCH NEXT FROM param_cursor INTO @ParamName, @DataType
        END

        CLOSE param_cursor
        DEALLOCATE param_cursor

        IF RIGHT(@ParamJSON, 1) = ','
            SET @ParamJSON = LEFT(@ParamJSON, LEN(@ParamJSON) - 1)
        SET @ParamJSON += N'}'

        RAISERROR(N'存储过程执行错误，传入参数: %s', 16, 1, @ParamJSON)
    END CATCH
END

注意事项 #

• 针对binary、varbinary等特殊数据类型，需在CASE语句中添加对应的格式转换逻辑。
• 表值参数需单独处理，需查询对应的用户定义表结构来提取内部值。
• 确保执行存储过程的账号拥有sys.parameters视图的查询权限。

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