旧病房驻留统计存储过程转表值函数及优化方案咨询

问题背景 #

我有一个多年前编写的存储过程，现在因原依赖对象不可用需要重构，想咨询两个问题：

1. 是否可以将其转换为表值函数？
2. 有没有更优的方法替代原存储过程？

需求目标 #

作为报表分析师，我希望能够在可配置的天数范围内，针对可配置的统计时点生成病房驻留报表。

具体规则：

• 输入StartDate（实际为DateTime类型，命名不规范）、EndDate（实际为DateTime类型）、统计时间@CensusTime，获取指定时间段内每天该时点的所有活跃驻留记录。
• 活跃驻留判定：统计时点@CensusDate需满足 WardStartDateTime <= @CensusDate，且WardEndDateTime >= @CensusDate或WardEndDateTime IS NULL。
• 示例：
  • 患者2023-01-01 14:01入院，不会被纳入当日14:00的统计；
  • 患者2023-01-01 13:59入院，会被纳入当日14:00的统计；
  • 若患者在统计周期第1天入院且周期结束后仍在院，则每天的统计时点都会记录该患者的活跃状态。

原存储过程代码 #

ALTER PROCEDURE [dbo].[HEY_spWardStaysAtCensusDate]
    @StartDate DATE,
    @EndDate DATE,
    @CensusTime TIME
AS
    DECLARE @NoOfDays INT,
            @LoopCounter INT,
            @CensusDate DATETIME

    SELECT 
        @NoOfDays = DATEDIFF(DAY, @StartDate, @EndDate) + 1

    IF OBJECT_ID('tempdb..#WardStays') IS NOT NULL
    BEGIN
        DROP TABLE #WardStays
    END

    CREATE TABLE #WardStays 
    (
        WARD_STAY_UNIQUE_ID INT NOT NULL,
        CENSUS_DATE_TIME DATETIME NOT NULL,
        LOCAL_PATIENT_NUMBER VARCHAR(50) NULL,
        WARD_CODE VARCHAR(50) NULL,
        WARD_START_DATE_TIME DATETIME NULL,
        WARD_END_DATE_TIME DATETIME NULL,
        TRUST_SITE_LOCAL VARCHAR(50) NULL,
        TRUST_SITE_NATIONAL VARCHAR(50) NULL,
        WARD_STAY_COUNTER INT NULL
    )

    SELECT @LoopCounter = 1
  
    WHILE @LoopCounter <= @NoOfDays
    BEGIN
        SET @CensusDate = DATEADD(DAY, @LoopCounter - 1, @StartDate) + CAST(@CensusTime AS DATETIME)

        INSERT INTO #WardStays (WARD_STAY_UNIQUE_ID, CENSUS_DATE_TIME, LOCAL_PATIENT_NUMBER, WARD_CODE, WARD_START_DATE_TIME,
WARD_END_DATE_TIME, TRUST_SITE_LOCAL, TRUST_SITE_NATIONAL, WARD_STAY_COUNTER)
            SELECT 
                IPWardStayID, @CensusDate, HEYNo, WardCode, 
                WardStartDateTime, WardEndDateTime, HospitalCode,
                HospitalNatCode, 1
            FROM 
                HealthBI_Views.dbo.IP_WARD_STAY
            WHERE 
                WardStartDateTime <= @CensusDate
                AND (WardEndDateTime >= @CensusDate OR WardEndDateTime IS NULL)

        SET @LoopCounter = @LoopCounter + 1
    END

    SELECT 
        WARD_STAY_UNIQUE_ID, CENSUS_DATE_TIME, 
        LOCAL_PATIENT_NUMBER, WARD_CODE, 
        WARD_START_DATE_TIME, WARD_END_DATE_TIME, 
        TRUST_SITE_LOCAL, TRUST_SITE_NATIONAL, WARD_STAY_COUNTER
    FROM 
        #WardStays

解决方案 #

1. 可以转换为表值函数 #

表值函数分为内联表值函数和多语句表值函数，这里更适合用内联表值函数——逻辑可通过纯查询实现，性能优于多语句函数和原存储过程的循环操作。

2. 更优方案：用日期生成器替代循环 #

原存储过程用WHILE循环逐天插入数据，效率较低。可以通过生成统计日期序列，再与病房驻留记录做关联查询，一次性得到所有结果，彻底避免循环。

具体实现代码 #

方案一：递归CTE生成日期序列（简洁易用）

CREATE FUNCTION [dbo].[ufn_WardStaysAtCensusDate]
(
    @StartDate DATE,
    @EndDate DATE,
    @CensusTime TIME
)
RETURNS TABLE
AS
RETURN
(
    -- 生成统计日期序列：从@StartDate到@EndDate每天的@CensusTime时点
    WITH DateSequence AS
    (
        SELECT CAST(@StartDate AS DATETIME) + CAST(@CensusTime AS DATETIME) AS CensusDateTime
        UNION ALL
        SELECT DATEADD(DAY, 1, CensusDateTime)
        FROM DateSequence
        WHERE CensusDateTime < CAST(@EndDate AS DATETIME) + CAST(@CensusTime AS DATETIME)
    )
    SELECT 
        ws.IPWardStayID AS WARD_STAY_UNIQUE_ID,
        ds.CensusDateTime AS CENSUS_DATE_TIME,
        ws.HEYNo AS LOCAL_PATIENT_NUMBER,
        ws.WardCode AS WARD_CODE,
        ws.WardStartDateTime AS WARD_START_DATE_TIME,
        ws.WardEndDateTime AS WARD_END_DATE_TIME,
        ws.HospitalCode AS TRUST_SITE_LOCAL,
        ws.HospitalNatCode AS TRUST_SITE_NATIONAL,
        1 AS WARD_STAY_COUNTER
    FROM HealthBI_Views.dbo.IP_WARD_STAY ws
    CROSS JOIN DateSequence ds
    WHERE 
        ws.WardStartDateTime <= ds.CensusDateTime
        AND (ws.WardEndDateTime >= ds.CensusDateTime OR ws.WardEndDateTime IS NULL)
    OPTION (MAXRECURSION 0) -- 统计天数超过100时必须添加，避免递归限制
)

方案二：数字表生成日期序列（超长期统计性能更优）

如果统计周期很长（比如超过1000天），递归CTE性能不如预先创建的数字表。可先创建数字表，再生成日期序列：

-- 一次性创建数字表（存储0到9999的数字）
CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY)
GO
INSERT INTO dbo.Numbers (Number)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
GO

-- 创建内联表值函数
CREATE FUNCTION [dbo].[ufn_WardStaysAtCensusDate]
(
    @StartDate DATE,
    @EndDate DATE,
    @CensusTime TIME
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        ws.IPWardStayID AS WARD_STAY_UNIQUE_ID,
        DATEADD(DAY, n.Number, CAST(@StartDate AS DATETIME)) + CAST(@CensusTime AS DATETIME) AS CENSUS_DATE_TIME,
        ws.HEYNo AS LOCAL_PATIENT_NUMBER,
        ws.WardCode AS WARD_CODE,
        ws.WardStartDateTime AS WARD_START_DATE_TIME,
        ws.WardEndDateTime AS WARD_END_DATE_TIME,
        ws.HospitalCode AS TRUST_SITE_LOCAL,
        ws.HospitalNatCode AS TRUST_SITE_NATIONAL,
        1 AS WARD_STAY_COUNTER
    FROM HealthBI_Views.dbo.IP_WARD_STAY ws
    CROSS JOIN dbo.Numbers n
    WHERE 
        DATEADD(DAY, n.Number, @StartDate) <= @EndDate
        AND ws.WardStartDateTime <= DATEADD(DAY, n.Number, CAST(@StartDate AS DATETIME)) + CAST(@CensusTime AS DATETIME)
        AND (ws.WardEndDateTime >= DATEADD(DAY, n.Number, CAST(@StartDate AS DATETIME)) + CAST(@CensusTime AS DATETIME) OR ws.WardEndDateTime IS NULL)
)

方案优势 #

• 避免循环操作，利用SQL集合运算特性，性能远高于原存储过程的WHILE循环；
• 内联表值函数可直接作为数据源被其他查询引用，比存储过程更灵活（支持直接JOIN或WHERE过滤）；
• 代码简洁、逻辑清晰，易于维护和修改。

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