如何将参数传递给 SQL (Excel) 中的查询

how to pass parameters to query in SQL (Excel)

提问by scatterbraiin

I "linked" Excel to Sql and it worked fine - I wrote some SQL script and it worked great. All I want to do is to pass parameter to query. Like every time I make refresh I want to be able to pass parameter (filter condition) to Sql Query. In "Connection Properties" Parameters button is disabled. So I can't make parameter query. Can Anyone help me?

我将 Excel“链接”到 Sql 并且运行良好 - 我编写了一些 SQL 脚本并且运行良好。我想要做的就是将参数传递给查询。就像每次刷新时，我都希望能够将参数（过滤条件）传递给 Sql Query。在“连接属性”参数按钮被禁用。所以我无法进行参数查询。谁能帮我？

采纳答案by Dave DuPlantis

It depends on the database to which you're trying to connect, the method by which you created the connection, and the version of Excel that you're using. (Also, most probably, the version of the relevant ODBC driver on your computer.)

这取决于您尝试连接的数据库、您创建连接的方法以及您使用的 Excel 版本。（此外，很可能是您计算机上相关 ODBC 驱动程序的版本。）

The following examples are using SQL Server 2008 and Excel 2007, both on my local machine.

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

When I used the Data Connection Wizard (on the Data tab of the ribbon, in the Get External Data section, under From Other Sources), I saw the same thing that you did: the Parameters button was disabled, and adding a parameter to the query, something like select field from table where field2 = ?, caused Excel to complain that the value for the parameter had not been specified, and the changes were not saved.

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

When I used Microsoft Query (same place as the Data Connection Wizard), I was able to create parameters, specify a display name for them, and enter values each time the query was run. Bringing up the Connection Properties for that connection, the Parameters... button is enabled, and the parameters can be modified and used as I think you want.

当我使用 Microsoft Query（与数据连接向导位于同一位置）时，我能够创建参数，为它们指定显示名称，并在每次运行查询时输入值。调出该连接的连接属性，启用参数...按钮，并且可以根据需要修改和使用参数。

I was also able to do this with an Access database. It seems reasonable that Microsoft Query could be used to create parameterized queries hitting other types of databases, but I can't easily test that right now.

我也可以使用 Access 数据库来做到这一点。Microsoft Query 可用于创建针对其他类型数据库的参数化查询似乎是合理的，但我现在无法轻松测试。

回答by mono código

This post is old enough that this answer will probably be little use to the OP, but I spent forever trying to answer this same question, so I thought I would update it with my findings.

这篇文章已经足够老了，这个答案可能对 OP 没有多大用处，但我花了很长时间试图回答同样的问题，所以我想我会用我的发现来更新它。

This answer assumes that you already have a working SQL query in place in your Excel document. There are plenty of tutorials to show you how to accomplish this on the web, and plenty that explain how to add a parameterized query to one, except that none seem to work for an existing, OLE DBquery.

此答案假定您的 Excel 文档中已经有一个可用的 SQL 查询。有很多教程可以向您展示如何在 Web 上完成此操作，还有很多教程解释了如何向其中添加参数化查询，但似乎没有一个教程适用于现有的 OLE DB查询。

So, if you, like me, got handed a legacy Excel document with a working query, but the user wants to be able to filter the results based on one of the database fields, and if you, like me, are neither an Excel nor a SQL guru, this might be able to help you out.

因此，如果您和我一样，收到了带有工作查询的旧版 Excel 文档，但用户希望能够根据数据库字段之一过滤结果，并且如果您像我一样既不是 Excel 也不是SQL 大师，这可能会帮助你。

Most web responses to this question seem to say that you should add a “?” in your query to get Excel to prompt you for a custom parameter, or place the prompt or the cell reference in [brackets] where the parameter should be. This may work for an ODBC query, but it does not seem to work for an OLE DB, returning “No value given for one or more required parameters” in the former instance, and “Invalid column name ‘xxxx'” or “Unknown object ‘xxxx'” in the latter two. Similarly, using the mythical “Parameters…” or “Edit Query…” buttons is also not an option as they seem to be permanently greyed out in this instance. (For reference, I am using Excel 2010, but with an Excel 97-2003 Workbook (*.xls))

对此问题的大多数网络回答似乎都说您应该添加“？” 在您的查询中让 Excel 提示您输入自定义参数，或将提示或单元格引用放在参数应位于的 [括号] 中。这可能适用于 ODBC 查询，但它似乎不适用于 OLE DB，在前一个实例中返回“没有为一个或多个必需参数提供值”，以及“无效的列名 'xxxx'”或“未知对象” 'xxxx'”在后两者中。同样，使用神话般的“参数...”或“编辑查询...”按钮也不是一种选择，因为在这种情况下它们似乎永久变灰。（作为参考，我使用的是 Excel 2010，但使用的是 Excel 97-2003 工作簿 (*.xls)）

What we can do, however, is add a parameter cell and a button with a simple routine to programmatically update our query text.

但是，我们可以做的是添加一个参数单元格和一个按钮，并通过一个简单的例程以编程方式更新我们的查询文本。

First, add a row above your external data table (or wherever) where you can put a parameter prompt next to an empty cell and a button (Developer->Insert->Button (Form Control) – You may need to enable the Developer tab, but you can find out how to do that elsewhere), like so:

首先，在外部数据表（或任何地方）上方添加一行，您可以在其中在空单元格和按钮旁边放置参数提示（Developer->Insert->Button (Form Control) – 您可能需要启用 Developer 选项卡，但您可以在其他地方找到如何做到这一点），如下所示：

Next, select a cell in the External Data (blue) area, then open Data->Refresh All (dropdown)->Connection Properties… to look at your query. The code in the next section assumes that you already have a parameter in your query (Connection Properties->Definition->Command Text) in the form “WHERE (DB_TABLE_NAME.Field_Name = ‘Default Query Parameter')” (including the parentheses). Clearly “DB_TABLE_NAME.Field_Name” and “Default Query Parameter” will need to be different in your code, based on the database table name, database value field (column) name, and some default value to search for when the document is opened (if you have auto-refresh set). Make note of the “DB_TABLE_NAME.Field_Name” value as you will need it in the next section, along with the “Connection name” of your query, which can be found at the top of the dialog.

接下来，在“外部数据”（蓝色）区域中选择一个单元格，然后打开“数据”->“全部刷新”（下拉列表）->“连接属性...”以查看您的查询。下一节中的代码假设您的查询（连接属性->定义->命令文本）中已经有一个格式为“WHERE (DB_TABLE_NAME.Field_Name = 'Default Query Parameter')”（包括括号）的参数。很明显，“DB_TABLE_NAME.Field_Name”和“默认查询参数”在你的代码中需要不同，基于数据库表名、数据库值字段（列）名称和一些在打开文档时搜索的默认值（如果你有自动刷新设置）。记下“DB_TABLE_NAME.Field_Name”值，因为您将在下一节中需要它，以及查询的“连接名称”，

Close the Connection Properties, and hit Alt+F11 to open the VBA editor. If you are not on it already, right click on the name of the sheet containing your button in the “Project” window, and select “View Code”. Paste the following code into the code window (copying is recommended, as the single/double quotes are dicey and necessary).

关闭连接属性，然后按 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

Replace “DB_TABLE_NAME.Field_Name” and "Connection name" (in two locations) with your values (the double quotes and the space and equals sign need to be included).

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

Replace "Cell reference" with the cell where your parameter will go (the empty cell from the beginning) - mine was the second cell in the first row, so I put “B1” (again, the double quotes are necessary).

将“单元格引用”替换为参数所在的单元格（开头的空单元格）——我的是第一行的第二个单元格，所以我放了“B1”（同样，双引号是必需的）。

Save and close the VBA editor.

保存并关闭 VBA 编辑器。

Enter your parameter in the appropriate cell.

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

Right click your button to assign the RefreshQuery sub as the macro, then click your button. The query should update and display the right data!

右键单击您的按钮以将 RefreshQuery 子指定为宏，然后单击您的按钮。查询应该更新并显示正确的数据！

Notes: Using the entire filter parameter name ("DB_TABLE_NAME.Field_Name =") is only necessary if you have joins or other occurrences of equals signs in your query, otherwise just an equals sign would be sufficient, and the Len() calculation would be superfluous. If your parameter is contained in a field that is also being used to join tables, you will need to change the "paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1" line in the code to "paramPosition = InStr(Right(.CommandText, Len(.CommandText) - InStrRev(.CommandText, "WHERE")), valueToFilter) + Len(valueToFilter) - 1 + InStr(.CommandText, "WHERE")" so that it only looks for the valueToFilter afterthe "WHERE".

注意：使用整个过滤器参数名称 ("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")" 这样它只查找 valueToFilter在“WHERE”之后。

This answer was created with the aid of datapig's “BaconBits” where I found the base code for the query update.

这个答案是在 datapig 的“BaconBits”的帮助下创建的，我在那里找到了查询更新的基本代码。