Power BI Direct Query模式下调用存储过程的原生查询创建咨询

方案1：转换为适配Direct Query的原生SQL查询 #

提供两种写法可选：

写法A：直接调用存储过程 #

直接通过EXEC执行存储过程，替换参数值即可使用：

EXEC [dbo].[TravelsCustomers] @TypeOp = 0, @Customer = '0'

使用要求：

• 已在Power BI对应数据源的权限设置中开启原生查询执行权限
• 存储过程已开启SET NOCOUNT ON（原存储过程已满足该要求）

写法B：展开存储过程逻辑为单条查询 #

避免临时表带来的查询折叠限制，更适配Direct Query模式，性能更优：

SELECT
    V.Estatus,
    V.ClaveCustomer,
    V.Lote,
    V.Address,
    V.OC,
    ISNULL(I2.Coment, '-') Coment,
    I2.ModifiedBy,
    DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), I2.CreatedDateTime) CreatedDateTime
FROM TravelVw V
LEFT JOIN (
    SELECT 
        I.Travel, 
        ISNULL(I.Coment, '-') Coment,
        I.ModifiedBy,
        I.CreatedDateTime
    FROM dbo.Travel I 
    WHERE I.CREATEDDATETIME = (
        SELECT MAX(CREATEDDATETIME) 
        FROM dbo.I3
        WHERE I.Travel = I3.Travel
    )
) I2 ON V.Travel = I2.Travel
WHERE   
    V.Estatus = 'PROGRESS'
    AND V.TypeOp = CASE 
        WHEN @TypeOp = 0 THEN V.TypeOp
        WHEN @TypeOp = 1 THEN 'FLASH'
        WHEN @TypeOp = 2 THEN 'SMALL'
    END
    AND ISNULL(@Customer, '0') IN ('0', V.ClaveCustomer)  
    AND V.TypeOp != 'SALE'

注：原存储过程中ClaveCustomer字段后缺少逗号的语法笔误，已在上述代码中修正。

方案2：更优长期方案：封装为表值函数 #

存储过程本身对Direct Query的适配性较差，将逻辑封装为表值函数后，可被Power BI原生识别，支持直接绑定报表参数，无需手写原生查询，查询折叠兼容性更好。
函数创建代码：

CREATE FUNCTION [dbo].[fn_GetTravelsCustomers]
(
    @TypeOp int = 0,
    @Customer varchar(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        V.Estatus,
        V.ClaveCustomer,
        V.Lote,
        V.Address,
        V.OC,
        ISNULL(I2.Coment, '-') Coment,
        I2.ModifiedBy,
        DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), I2.CreatedDateTime) CreatedDateTime
    FROM TravelVw V
    LEFT JOIN (
        SELECT 
            I.Travel, 
            ISNULL(I.Coment, '-') Coment,
            I.ModifiedBy,
            I.CreatedDateTime
        FROM dbo.Travel I 
        WHERE I.CREATEDDATETIME = (
            SELECT MAX(CREATEDDATETIME) 
            FROM dbo.I3
            WHERE I.Travel = I3.Travel
        )
    ) I2 ON V.Travel = I2.Travel
    WHERE   
        V.Estatus = 'PROGRESS'
        AND V.TypeOp = CASE 
            WHEN @TypeOp = 0 THEN V.TypeOp
            WHEN @TypeOp = 1 THEN 'FLASH'
            WHEN @TypeOp = 2 THEN 'SMALL'
        END
        AND ISNULL(@Customer, '0') IN ('0', V.ClaveCustomer)  
        AND V.TypeOp != 'SALE'
)

使用方法：Direct Query模式连接SQL Server后，直接在数据库对象列表中勾选该函数加载，即可在Power BI中绑定参数调用。

通用注意事项 #

• 需确保Power BI连接所用的SQL Server账号，对涉及的存储过程、视图、表、函数有对应的执行/查询权限
• Direct Query模式下建议尽量减少返回的不必要字段，降低查询下发的性能损耗

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