Power BI直连模式下SQL存储过程转表值函数语法报错求助

报错原因说明 #

• 错误1：你声明的是内联表值函数（直接写RETURNS TABLE），这种函数不支持BEGIN...END代码块，也不支持内部变量赋值操作，只能写单条SELECT语句
• 错误2：你把内部计算用的@AnioMes、@AnioMes6放到了输入参数列表里，这两个变量不需要外部传参，应该放到函数内部声明
• 错误3：RETURN语句位置错误，多语句表值函数需要先定义返回表结构，将查询结果插入后再执行RETURN

修正后的函数代码 #

CREATE FUNCTION [dbo].[FN_Test] -- 建议不要用SP前缀，SP是存储过程命名惯例，函数可使用FN前缀区分
(
    @Anio int = NULL, -- 直接设置默认值，兼容参数为空的场景
    @Mes int = NULL
)
RETURNS @Result TABLE -- 定义返回表结构，和输出字段一一对应，字段长度可按实际表结构调整
(
    AnioMes int,
    FECHA_CREACION datetime,
    INPUTSUBJECT nvarchar(255),
    TOWTYPE char(2),
    VENDNAME nvarchar(255)
)
AS
BEGIN
    DECLARE @AnioMes varchar(8),
            @AnioMes6 varchar(8)

    -- 处理参数为空的情况
    IF @Anio IS NULL OR @Mes IS NULL
    BEGIN
        SELECT @Anio = YEAR(GETDATE()),
               @Mes = MONTH(GETDATE())
    END

    -- 计算两个日期变量，逻辑和原存储过程完全一致
    SELECT @AnioMes = (CASE WHEN @Mes = 12 THEN @Anio + 1 ELSE @Anio END * 100 + 
                      CASE WHEN @Mes = 12 THEN 1 ELSE @Mes + 1 END) * 100 + 1
    SELECT @AnioMes6 = CONVERT(varchar(8), DATEADD(mm, -5, @AnioMes), 112)

    -- 将查询结果插入返回表
    INSERT INTO @Result
    SELECT 
        YEAR(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) * 100 + 
        MONTH(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) AS AnioMes,
        DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) AS FECHA_CREACION,
        INPUTSUBJECT,
        CASE 
            WHEN TOWTYPE = 1 THEN 'T1'
            WHEN TOWTYPE = 2 THEN 'T2'
            WHEN TOWTYPE = 3 THEN 'T3'
            WHEN TOWTYPE = 4 THEN 'T4'
            ELSE ''
        END AS TOWTYPE,
        [VENDNAME]
    FROM 
        TRUCKS
    WHERE
        YEAR(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) >= YEAR(@AnioMes6)
        AND MONTH(DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME)) <= MONTH(@AnioMes)
        AND DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), CREATEDDATETIME) >= '20160701'

    RETURN
END

Power BI Direct Query调用说明 #

调用格式为SELECT * FROM dbo.FN_Test(2024, 5)，如果需要使用默认参数（自动取当前年月），写SELECT * FROM dbo.FN_Test(DEFAULT, DEFAULT)即可，调用前确认数据源账号有自定义函数的执行权限。

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