如何为采集SQL Server等待数据的存储过程添加参数实现按需返回列

方案一：动态SQL拼接（灵活性最高，推荐30+列场景使用） #

实现思路：新增一个字符串类型的入参接收用户指定的列名列表，先校验传入的列名都是sys.dm_exec_requests的合法列之后，再拼接成动态SQL执行，既保证灵活性又避免SQL注入风险。

CREATE OR ALTER PROCEDURE dbo.usp_GetWaitEventData
    @SelectColumns NVARCHAR(MAX) = N'session_id, start_time, status, wait_type, wait_time, command' -- 可自定义默认返回的常用列
AS
BEGIN
    SET NOCOUNT ON;

    -- 校验传入的列名合法性，防止SQL注入
    DECLARE @ValidColumns NVARCHAR(MAX);
    SELECT @ValidColumns = STRING_AGG(QUOTENAME(c.name), N',')
    FROM sys.columns c
    JOIN sys.objects o ON c.object_id = o.object_id
    WHERE o.name = N'dm_exec_requests' AND o.schema_id = SCHEMA_ID(N'sys')
      AND CHARINDEX(N',' + c.name + N',', N',' + REPLACE(@SelectColumns, N' ', N'') + N',') > 0;

    -- 校验无合法列时返回默认列
    IF @ValidColumns IS NULL OR @ValidColumns = N''
        SET @ValidColumns = N'session_id, start_time, status, wait_type, wait_time, command';

    -- 拼接执行动态SQL
    DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT ' + @ValidColumns + N'
    FROM sys.dm_exec_requests
    WHERE session_id > 50 -- 可根据需求调整过滤条件，比如过滤系统会话
    ';

    EXEC sp_executesql @SQL;
END
GO

使用示例：

-- 自定义选择需要返回的列
EXEC dbo.usp_GetWaitEventData @SelectColumns = N'session_id, wait_type, wait_time, cpu_time, last_wait_type'

该方案优势：仅需1个参数即可支持任意列组合选择，后续新增列也不需要修改存储过程参数定义，适配性强。

方案二：开关参数控制（无注入风险，适合固定可选列场景） #

实现思路：为每个可选列定义一个BIT类型的开关参数，用户可以通过传1/0控制对应列是否返回，完全不需要拼接动态SQL。

CREATE OR ALTER PROCEDURE dbo.usp_GetWaitEventData_StaticParam
    @ShowSessionID BIT = 1,
    @ShowWaitType BIT = 1,
    @ShowWaitTime BIT = 1,
    @ShowCPUTime BIT = 0,
    @ShowStartTime BIT = 0
    -- 剩余可选列依次新增对应开关参数即可
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        CASE WHEN @ShowSessionID = 1 THEN session_id END AS session_id,
        CASE WHEN @ShowWaitType = 1 THEN wait_type END AS wait_type,
        CASE WHEN @ShowWaitTime = 1 THEN wait_time END AS wait_time,
        CASE WHEN @ShowCPUTime = 1 THEN cpu_time END AS cpu_time,
        CASE WHEN @ShowStartTime = 1 THEN start_time END AS start_time
        -- 剩余列依次添加CASE判断逻辑
    FROM sys.dm_exec_requests
    WHERE session_id > 50;
END
GO

该方案优缺点：优点是完全无SQL注入风险，逻辑简单易维护；缺点是30多列需要对应定义30多个参数，调用时选择多列需要传多个参数值，适合可选列数量少、使用场景固定的场景。

配套优化建议 #

• 定时采集逻辑可以保留全量列写入中间表，对外查询的存储过程从中间表取数筛选列，避免频繁查询动态管理视图占用数据库资源
• 可以新增额外的过滤参数，比如@MinWaitTime INT、@ExcludeSystemSession BIT等，进一步满足用户的筛选需求

内容的提问来源于stack exchange，提问作者Newbie-DBA