如何在创建SQL视图时使用DECLARE声明变量解决语法报错

SQL Server 视图的定义逻辑中不允许使用DECLARE声明变量，视图仅支持基于查询语句的结构定义，不能包含变量声明、流程控制等批处理语法，因此你会触发对应语法报错。

方案1：将变量内联到CTE中（最适配你当前的PowerBI仪表盘需求） #

直接把两个变量的取值逻辑替换到CTE的对应位置，无需声明变量，修改后的视图定义代码如下：

CREATE OR ALTER VIEW vw_NonApprovedTests AS
WITH OrderDays as 
( 
    SELECT 
        CalendarDate = (SELECT MIN(ActionOn) FROM WFD)
    UNION ALL 
    SELECT 
        CalendarDate = DATEADD(MONTH, 1, CalendarDate)
    FROM 
        OrderDays WHERE DATEADD (MONTH, 1, CalendarDate) <= GETDATE()
),
Calendar AS
(
    SELECT
        EndOfMonth =  EOMONTH (CalendarDate) 
    FROM
        OrderDays                 
) 
-- 下方替换为你原本的SELECT查询逻辑
SELECT etc.......

注意事项 #

如果WFD表中最早的ActionOn距离当前时间超过100个月（即8年以上），递归CTE默认的100层递归限制会触发报错，你在PowerBI中拉取数据时使用自定义查询加载即可解除限制：

SELECT * FROM vw_NonApprovedTests OPTION (MAXRECURSION 0)

该方案生成的是普通视图，PowerBI可以直接识别加载，无需额外配置。

方案2：使用内嵌表值函数（需要灵活调整查询时间段时选用） #

如果后续你需要灵活自定义起止日期查询，可以用表值函数替代视图，代码如下：

CREATE OR ALTER FUNCTION fn_NonApprovedTests
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS TABLE 
AS
RETURN
(
    WITH OrderDays as 
    ( 
        SELECT 
            CalendarDate = @StartDate
        UNION ALL 
        SELECT 
            CalendarDate = DATEADD(MONTH, 1, CalendarDate)
        FROM 
            OrderDays WHERE DATEADD (MONTH, 1, CalendarDate) <= @EndDate
    ),
    Calendar AS
    (
        SELECT
            EndOfMonth =  EOMONTH (CalendarDate) 
        FROM
            OrderDays                 
    ) 
    -- 下方替换为你原本的SELECT查询逻辑
    SELECT etc.......
)

调用时直接传入参数即可：

SELECT * FROM fn_NonApprovedTests('2020-01-01', GETDATE())

PowerBI也支持直接导入表值函数，你可以在导入时指定参数值，或者在Power Query中动态传参。

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