PowerQuery中使用SQL Server存储过程能否实现查询折叠？

是否支持原生查询折叠？ #

直接通过EXEC [my_procedure] '20240831','20240831'调用带参数的存储过程时，Power Query不支持查询折叠。原因是存储过程属于封装的黑盒逻辑，Power Query无法解析其内部执行的SQL语句，也就无法将后续的M代码操作（如筛选、排序）转换为等价的SQL推送到数据库执行。

有效实现查询折叠的方法 #

1. 重构为内联表值函数（ITVF）—— 推荐方案

将存储过程的业务逻辑重构为内联表值函数，这类函数的返回结果是结构化的表，Power Query可以完全识别并支持查询折叠。

示例SQL函数定义：

CREATE FUNCTION dbo.my_date_filter_function(@fromdate DATE, @todate DATE)
RETURNS TABLE
AS
RETURN (
    -- 替换为原存储过程中的核心查询逻辑
    SELECT col1, col2, date_col
    FROM your_target_table
    WHERE date_col BETWEEN @fromdate AND @todate
)

在Power Query中调用时，使用如下SQL语句：

SELECT * FROM dbo.my_date_filter_function('20240831','20240831')

后续在Power Query中添加的筛选、排序、聚合等操作，都会自动折叠为对应的SQL语句，在数据库端执行，保证性能。

2. 参数化SQL查询（有限支持）

如果无法修改现有存储过程，可以通过参数化SQL结合Power Query参数传递的方式，实现部分场景下的优化（虽不能完全支持所有折叠操作，但比直接调用更可控）。

步骤：

• 在Power Query中创建两个日期类型参数（如FromDate、ToDate）。
• 选择"从SQL查询"连接数据源，输入带占位符的SQL：

  DECLARE @from DATE = ?, @to DATE = ?;
  EXEC [my_procedure] @from, @to;
  

• 将Power Query的参数绑定到SQL中的?占位符。
• 注意：这种方式下，后续对结果集的操作（如额外筛选）无法折叠到数据库，仍会在Power Query端处理。

3. 使用OPENQUERY调用（特定场景适用）

如果存储过程内部仅包含简单的SELECT逻辑（无临时表、游标等复杂操作），可以尝试用OPENQUERY封装存储过程调用，让Power Query识别到底层的查询结构：

SELECT * FROM OPENQUERY(Your_SQL_Server_Name, 'EXEC [my_procedure] ''20240831'',''20240831''')

这种方法的稳定性依赖存储过程的内部实现，仅建议在无法重构为表值函数时临时使用。

关键注意点 #

• 查询折叠的核心是Power Query能将M代码转换为可执行的SQL，存储过程的封装特性导致原生调用无法满足这一条件。
• 内联表值函数是实现完全查询折叠的最优选择，同时能保证数据库端的执行性能。

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