如何将参数传递给SQL（Excel）中的查询 [英] how to pass parameters to query in SQL (Excel)

问题描述

我将Excel链接到Sql，它工作正常 - 我写了一些SQL脚本，它的工作很棒。我想做的就是将参数传递给查询。像每次刷新一样，我希望能够将参数（过滤条件）传递给Sql Query。
在连接属性参数按钮被禁用。所以我无法进行参数查询。
任何人可以帮助我吗？

解决方案

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

以下示例正在使用SQL Server 2008和Excel 2007，无论是在本地计算机上

当我使用数据连接向导（在功能区的数据选项卡上的获取外部数据部分中，来自其他来源下）时，我看到了同样的事情您已经做了以下操作：参数按钮被禁用，并且在查询中添加一个参数，像 select field from table where field2 =？，导致Excel抱怨该参数未被指定，并且更改未被保存。

当我使用Microsoft Query（与数据连接向导相同的位置）时，我能够创建参数，为其指定显示名称，并在每次运行查询时输入值。启动该连接的连接属性，参数...按钮被启用，参数可以按照我想要的修改和使用。

我也是能够使用Access数据库执行此操作。 Microsoft Query可以用来创建参数化的查询命中其他类型的数据库，但我现在无法轻松测试，似乎很合理。

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?

解决方案

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.)

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

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.

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.

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.

这篇关于如何将参数传递给SQL（Excel）中的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！