sql-server - 在 Power Query 中将参数从 Excel 传递到 SQL

Question

我想设置局部变量或将参数从 Excel 传递到 SQL。我发现了类似的问题，但都提到了旧版本的 Excel 和/或答案显示了如何在 Power Query 编辑器中筛选或操作通用 SQL 查询的输出，而不是传递参数或修改 SQL，以便SQL Server 以所需的形式提供数据。

我正在构建一个大型 Microsoft Excel 电子表格，它依赖于十个不同的 SQL 查询，所有查询都针对一个公共(public) SQL Server 数据库。 Excel 和 SQL Server 安装在我的笔记本电脑上，并且是最新版本(截至 2022 年 3 月 16 日)。所有十个查询共享一个共同的日期限制，强加在查询的 WHERE 子句中。访问的表和输出的形式非常不同，因此没有简单的方法将十个查询组合成一个查询。查询包含多个聚合级别(例如 SUM(...))，因此我需要限制在聚合之前访问的记录并将结果从查询传回 Excel。

如目前所写，每个查询都以在局部变量中设置两个日期值开始。例如，

 DECLARE @BEGIN_DATE AS smalldatetime;  
 DECLARE @END_DATE AS smalldatetime;
 @BEGIN_DATE = CAST('2021-03-01 00:00' AS smalldatetime);
 @END_DATE = CAST('2021-03-02 23:59' AS smalldatetime);

十个查询中的每一个都在 WHERE 子句中包含一行类似于

WHERE
    PickUpDate BETWEEN @BEGIN_DATE AND @END_DATE

每个查询都将使用同一对日期。但是，筛选的列(上面的 PickUpDate)从一个查询更改为下一个查询。

实际上，我必须手动编辑十个查询中的每一个以更改两个日期——总共编辑了二十次。这是耗时且容易出错的。理想情况下，我想在电子表格、弹出对话框或任何其他方便的方式中设置日期范围，并将日期传递给 SQL 查询。然后在 Excel 中选择“数据”>“全部刷新”，一次更新我的所有表格。

这可能吗？如果可能，如何做到？

Answer 1

David Browne 的回答大体上是中肯的。但我发现将 Excel 表中的数据直接读取到 SQL 中有些困难，因为最新版本的 Excel/Power Query 中存在安全限制。此外，由于这是我第一次直接在 M 代码和高级编辑器中工作，因此填补空白非常具有挑战性。

我终于得到了一个很好的解决方案；这对我有用。

首先，我将参数值存储在一个包含两列的表中。我的表名为“ParameterTable”，列标题名为“Parameter_Name”和“Value”。要传递给 SQL Server 的值存储在值列中。我的表格有两行，行条目标记为“Begin_DateTime”和“End_DateTime”。

其次，我创建了一个名为“ftnGetParameter”的可调用函数。选择“数据”>“获取数据”>“来自其他来源”>“空白查询”。然后选择“高级编辑器”。删除Excel添加的任何样板，并进入并保存此功能

let theParameter=(TableName,ParameterLabel) =>
let 
    Source=Excel.CurrentWorkbook(){[Name=TableName]}[Content],
    value = Source{[Parameter_Name=ParameterLabel]}[Value]
in 
    value 
in 
    theParameter

第三，照常编写 SQL 语句。我试图将日期传递给 SQL，所以我最初使用字符串文字进行编码。以通常的方式输入查询。我使用数据 > 获取数据 > 从数据库 > 从 SQL Server 数据库。然后粘贴在SQL中。我查询中的两个相关行如下所示:

DECLARE @BEGIN_DATE AS SMALLDATETIME='2021-01-01 00:00';
DECLARE @END_DATE AS SMALLDATETIME='2021-12-31 23:59';

您可以跳过这一步，但它允许我在调用函数传递参数之前输入、格式化和运行复杂的 SQL 代码。

最后，只需将 SQL 中的字符串文字替换为调用函数的代码即可。我的前几行 M 代码如下所示:

let
    Source = Sql.Database("DESKTOP-04P8E8C", "nfbdata", 
[Query=
" 
DECLARE @BEGIN_DATE AS SMALLDATETIME= '" & ftnGetParameter("ParameterTable","Begin_DateTime") & "';
DECLARE @END_DATE AS SMALLDATETIME='" & ftnGetParameter("ParameterTable","End_DateTime") & "'  (… the query continues )

Excel 将发出一些关于运行查询的警告并提示您编辑权限。授予权限后，该函数将从参数表中读取文本并将其传递到 SQL。

我发现函数调用不是可选的。显然，将代码直接导入到对 SQL Server 的 native 调用中被认为是 Not Acceptable 安全风险。

非常感谢 David Browne 先生。他的帖子绝对指向了正确的方向。