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

查看:29
本文介绍了如何将参数传递给 SQL (Excel) 中的查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我将 Excel链接"到 Sql 并且运行良好 - 我编写了一些 SQL 脚本并且运行良好.我想要做的就是将参数传递给查询.就像每次刷新时,我都希望能够将参数(过滤条件)传递给 Sql 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?

推荐答案

这取决于您尝试连接的数据库、您创建连接的方法以及您使用的 Excel 版本.(此外,最有可能的还有您计算机上相关 ODBC 驱动程序的版本.)

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

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

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

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

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.

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

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.

我还可以使用 Access 数据库执行此操作.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.

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆