如何在SQL（Excel）中向查询传递参数

Question

我将Excel与Sql进行了“链接”，很好地完成了操作-编写了一些SQL脚本并且它们也非常出色的运行。我想做的只是将参数传递给查询。就像每次刷新时，我希望能够传递参数（筛选条件）到Sql查询中。

但是在“连接属性”中，“参数”按钮是禁用状态。所以我无法制作参数查询。有人可以帮帮我吗？

- scatterbraiin

---

你需要更加精确，比如我们正在谈论什么类型的SQL？（MS SQL Server、Oracle等）。然而，在任何情况下，你都可以阅读有关存储过程的内容，这是在SQL中传递参数的方式，除非你准备一个字符串，可以动态更新，然后执行它。 - Christian Bøhlke

Answer 1

这篇文章可能对原帖发布者已经没有什么用处了。但是我也曾经为答案苦苦寻觅，所以我认为我可以分享一下我找到的方法。

本文假设你已经在Excel文档中有一个可用的SQL查询。网络上有很多教程可以教你如何实现这一点，也有很多教程可以教你如何添加参数化查询，只不过似乎没有适用于现有OLE DB查询的教程。

因此，如果像我一样，你手头有一个带有可用查询的传统Excel文档，但用户想要能够根据数据库字段过滤结果，而你既不是Excel大神也不是SQL大牛，那么这篇文章或许可以帮助你。

对于这个问题，大多数Web响应都建议在查询中添加“？”以便Excel提示你输入自定义参数，或将提示或单元格引用放在[方括号]中以表示参数。这种方法可能适用于ODBC查询，但似乎不适用于OLE DB查询，在前一种情况下返回“未给出一个或多个必需参数的值”，在后两种情况下返回“无效的列名‘xxxx’”或“未知的对象‘xxxx’”。同样，在此情况下使用神秘的“Parameters…”或“Edit Query…”按钮也不是一个选择，因为它们似乎永久灰显。 （参考资料，我使用的是Excel 2010，但用的是Excel 97-2003工作簿（*.xls））

然而，我们可以添加一个参数单元格和一个按钮，并使用简单的程序来编程更新查询文本。

首先，在你的外部数据表格（或任何其他地方）上面添加一个行，其中你可以在空单元格旁边放置一个参数提示和一个按钮（开发人员->插入->按钮（表单控件）-你可能需要启用开发人员选项卡，但你可以在其他地方找到如何做到这一点），如下所示：

接下来，选择外部数据（蓝色）区域中的一个单元格，然后打开Data->全部刷新（下拉菜单）->连接属性…查看你的查询。下一节中的代码假设你的查询（连接属性->定义->命令文本）中已经有一个参数，格式为“WHERE (DB_TABLE_NAME.Field_Name = ‘默认查询参数')”（包括括号）。显然，“DB_TABLE_NAME.Field_Name”和“默认查询参数”在你的代码中将需要根据数据库表名、数据库值字段（列）名以及文档打开时要搜索的某个默认值进行更改（如果已设置自动刷新）。记下“DB_TABLE_NAME.Field_Name”值，因为你在下一节中将需要它，以及查询的“连接名称”，该名称可以在对话框顶部找到。

关闭连接属性，然后按Alt+F11打开VBA编辑器。如果你还没有打开，右键单击包含按钮的工作表名称，在“项目”窗口中选择“查看代码”。将以下代码粘贴到代码窗口中（建议复制，因为引号很微妙且必要）。

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

将“DB_TABLE_NAME.Field_Name”和“Connection name”（两个位置）替换为您的值（需要包括双引号、空格和等号符）。

将“Cell reference”替换为您的参数所在的单元格（从开头的空单元格开始）-我的是第一行的第二个单元格，因此我放置了“B1”（同样，需要双引号）。

保存并关闭VBA编辑器。

在适当的单元格中输入您的参数。

右键单击按钮，将RefreshQuery子程序分配为宏，然后单击按钮。查询应该更新并显示正确的数据！

注：如果您的查询中有连接或其他等号符出现，则只使用整个筛选参数名称（“DB_TABLE_NAME.Field_Name =”）才是必要的，否则只用一个等号符就足够了，Len()计算将是多余的。如果您的参数包含在也用于连接表的字段中，则需要将代码中的“paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1”行更改为“paramPosition = InStr（Right(.CommandText，Len(.CommandText) - InStrRev(.CommandText，“WHERE”))，valueToFilter）+ Len(valueToFilter)-1+InStr(.CommandText，“WHERE” )”，以便它只查找“WHERE”之后的valueToFilter。

本答案借助于datapig的“BaconBits”，其中我找到了查询更新的基本代码。

- mono código

---

@Mowgli 是的，它可以工作，但正如我在最后一段中所指出的那样，如果您试图基于用于连接的相同字段进行过滤（参数化），则必须更改paramPosition设置。 - mono código

一旦从有效的外部数据源形成了表格，参数按钮就会变为可用状态...因此，如果您在不使用参数的情况下填充了表格，然后返回到连接属性，您应该可以看到可用于设置的参数按钮。(别忘了在SQL查询中使用fieldname=?) - Assad Ebrahim

@AssadEbrahim 在我所讨论的文件中似乎并非如此。我在这些文件中有有效且可运行的查询，但参数按钮仍然无法使用。尝试向查询文本添加参数提示符（?）会产生错误。你的连接类型是OLE DB吗？ - mono código

@monocódigo：ODBC。回应了“这可能适用于ODBC连接……”--想确认它确实适用，但乍一看即使对于ODBC，功能也似乎被禁用了。 - Assad Ebrahim

@monocódigo：出于好奇，您使用（或更喜欢使用）OLE DB而不是ODBC的原因是什么？ - Assad Ebrahim

Answer 2

这取决于你要连接的数据库、创建连接的方法以及你使用的Excel版本（还有最有可能你计算机上相关ODBC驱动程序的版本）。

以下示例使用我本地机器上的SQL Server 2008和Excel 2007。

当我使用数据连接向导（在“数据”选项卡上，在“来自其他来源”下的“获取外部数据”部分中）时，我看到与您看到的相同情况：参数按钮被禁用，并且将参数添加到查询中（例如select field from table where field2 = ?）会导致Excel抱怨未指定参数的值，并且更改没有保存。

当我使用Microsoft Query（与数据连接向导相同的位置）时，我能够创建参数，为它们指定显示名称，并在每次运行查询时输入值。打开该连接的连接属性时，参数按钮处于启用状态，可以修改参数并像您想要的那样使用它们。

我还能够在Access数据库中做到这一点。Microsoft Query似乎可以用于创建针对其他类型数据库的带参数查询，但我现在不能轻松测试这一点。

- Dave DuPlantis

Answer 3

好吧，我意识到这个问题很旧，但是我不得不努力寻找一个适用于Excel中参数化SQL查询的答案，而这是我今天找到的最简洁的方法。希望它能帮助更多的人。首先，确保你已经启用了Power Query（https://powerquery.microsoft.com/en-us/excel/）。使用"获取数据"和SQL查询选项，填写服务器名称并添加SQL查询代码。

完成这个步骤后，你可以在Excel文件中创建一个虚拟表格，该表格可以接受用户提供的参数，并将它们传递给Power Query以过滤行数；例如，在这里我使用了开始日期和结束日期作为参数。

在Power Query中创建用户定义参数的步骤如下（选择“使用单元格值筛选数据”选项）：https://support.microsoft.com/en-us/office/create-a-parameter-query-power-query-5eb365bc-3982-4ab2-8830-b205a69e0f33。虽然需要一些努力，但比上述的VBA方法要简单得多。

- Plato77