如何在SQL存储过程中全局访问CTE生成的工作日数据集？

CTE的作用域仅限定义它的单个SQL语句块，所以存储过程里后续逻辑无法直接引用。以下是几种可行的解决方法：

方法1：使用临时表（推荐数据量较大的场景） #

将CTE的结果存入临时表，临时表在整个存储过程生命周期内都可访问：

-- 定义CTE并将结果写入临时表
WITH WorkdaysCTE AS (
    -- 你的原CTE逻辑：生成从次日到ASSYDAY表最后日期的工作日
    SELECT 你的工作日字段 AS WorkDate
    FROM ... -- 替换为你的实际查询逻辑
)
SELECT * INTO #TempWorkdays FROM WorkdaysCTE;

-- 后续查询直接引用临时表
SELECT *
FROM 目标表
WHERE ASSYDATE IN (SELECT WorkDate FROM #TempWorkdays);

-- 可选：存储过程结束前手动清理临时表（SQL Server会自动销毁会话级临时表）
DROP TABLE IF EXISTS #TempWorkdays;

方法2：使用表变量（适合数据量较小的场景） #

表变量的作用域覆盖整个存储过程，语法与临时表类似：

DECLARE @WorkdaysTable TABLE (WorkDate DATE);

WITH WorkdaysCTE AS (
    -- 你的原CTE逻辑
    SELECT 你的工作日字段 AS WorkDate
    FROM ...
)
INSERT INTO @WorkdaysTable (WorkDate)
SELECT WorkDate FROM WorkdaysCTE;

-- 后续查询引用表变量
SELECT *
FROM 目标表
WHERE ASSYDATE IN (SELECT WorkDate FROM @WorkdaysTable);

方法3：创建持久化视图（适合固定逻辑的跨对象复用） #

如果生成工作日的逻辑固定、无需动态参数，可以将CTE转换为视图，这样不仅当前存储过程，其他数据库对象也能调用：

-- 仅需执行一次创建视图的操作
CREATE VIEW vw_Workdays AS
WITH WorkdaysCTE AS (
    -- 你的原CTE逻辑
    SELECT 你的工作日字段 AS WorkDate
    FROM ...
)
SELECT WorkDate FROM WorkdaysCTE;

-- 存储过程中直接引用视图
SELECT *
FROM 目标表
WHERE ASSYDATE IN (SELECT WorkDate FROM vw_Workdays);

方法4：使用表值函数（适合带动态参数的复用场景） #

如果需要动态传入日期范围参数，可创建表值函数：

-- 创建表值函数
CREATE FUNCTION fn_GetWorkdays (@StartDate DATE, @EndDate DATE)
RETURNS TABLE
AS
RETURN (
    WITH WorkdaysCTE AS (
        -- 基于传入参数生成工作日的逻辑
        SELECT 你的工作日字段 AS WorkDate
        FROM ...
        WHERE 日期字段 BETWEEN @StartDate AND @EndDate
    )
    SELECT WorkDate FROM WorkdaysCTE
);

-- 存储过程中调用函数
SELECT *
FROM 目标表
WHERE ASSYDATE IN (
    SELECT WorkDate 
    FROM dbo.fn_GetWorkdays(DATEADD(DAY,1,GETDATE()), (SELECT MAX(日期字段) FROM ASSYDAY))
);

注意事项 #

• 临时表（#开头）是会话级别的，同一会话下的其他存储过程也能访问；全局临时表（##开头）可跨会话，但易引发冲突，不推荐使用。
• 表变量在数据量大时性能不如临时表，因为表变量没有统计信息，查询优化器可能生成低效执行计划。
• 视图和表值函数适合复用性高的场景，可避免重复编写CTE逻辑。

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