求助：编写带多条件的SQL存储过程计算Graph表指定列平均值

已创建的Graph表结构 #

CREATE TABLE [dbo].[Graph](
    [RequestID] [int] NOT NULL,
    [ModuleName] [nvarchar](50) NULL,
    [Info] [float] NULL,
    [HR] [float] NULL,
    [SR] [float] NULL,
    [ISIM] [float] NULL,
    [RequestCreatedDate] [datetime2](7) NULL,
    [RequestModifiedDate] [datetime2](7) NULL
)

表中现有数据 #

需求说明 #

• 当@module_name为NULL时，计算全表Info、HR、SR、ISIM列的平均值；
• 当@module_name为CSE时，仅计算ModuleName为CSE的行中上述列的平均值；
• 当@start_date和@end_date均为NULL时，统计所有时间段的记录；
• 当@start_date不为NULL、@end_date为NULL时，统计从指定起始日期到当前的记录；
• 当@start_date和@end_date均不为NULL时，统计指定日期范围内的记录；
• 当@start_date为NULL、@end_date不为NULL时，统计从最早日期到指定结束日期的记录。

原存储过程问题分析 #

你编写的存储过程存在以下问题：

1. 错误地将@where1声明为datetime2类型，应为字符串类型；
2. 日期条件使用=而非范围判断（>=/<=），不符合时间段统计需求；
3. 拼接WHERE子句时逻辑混乱，多次添加WHERE关键字会导致SQL语法错误；
4. 重复执行sp_executesql，且参数传递不完整；
5. 日期参数调用格式错误，未用单引号包裹日期字符串。

修正后的存储过程 #

ALTER PROCEDURE avg_calc (
    @module_name NVARCHAR(50) = NULL,
    @start_date DATETIME2 = NULL,
    @end_date DATETIME2 = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @where_clause NVARCHAR(MAX) = '';

    -- 基础查询语句
    SET @sql = N'
        SELECT 
            AVG(Info) AS Info,
            AVG(HR) AS HR,
            AVG(SR) AS SR,
            AVG(ISIM) AS ISIM
        FROM Graph
        WHERE 1=1 ';

    -- 拼接模块条件
    IF @module_name IS NOT NULL
    BEGIN
        SET @where_clause += N' AND ModuleName = @_module_name ';
    END

    -- 拼接日期范围条件
    IF @start_date IS NOT NULL
    BEGIN
        SET @where_clause += N' AND RequestCreatedDate >= @_start_date ';
    END
    IF @end_date IS NOT NULL
    BEGIN
        SET @where_clause += N' AND RequestCreatedDate <= @_end_date ';
    END
    -- 处理仅传开始日期的情况：自动将结束时间设为当前时间
    IF @start_date IS NOT NULL AND @end_date IS NULL
    BEGIN
        SET @where_clause += N' AND RequestCreatedDate <= GETDATE() ';
    END

    -- 拼接WHERE子句到主SQL
    SET @sql += @where_clause;

    -- 执行动态SQL，传递所有参数
    EXEC sp_executesql 
        @sql,
        N'@_module_name NVARCHAR(50), @_start_date DATETIME2, @_end_date DATETIME2',
        @_module_name = @module_name,
        @_start_date = @start_date,
        @_end_date = @end_date;
END

测试示例 #

1. 统计CSE模块的平均值 #

EXEC avg_calc @module_name = 'CSE';

返回结果：Info平均值为4.666...，HR为3，SR为3，ISIM为2.333...

2. 统计2023-02-02到2023-04-30的记录平均值 #

注意：日期参数需用单引号包裹，避免无效日期（如30-02-2023不存在）

EXEC avg_calc @start_date = '2023-02-02', @end_date = '2023-04-30';

该范围包含RequestID 1003、1004、1005三条记录。

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