使用SQL查询中的参数和子查询

I have a fairly complicated SQL query with a nested subquery. When I try to use parameters in Microsoft Query is say I can use parameters in queries that cant be represented graphically. So I need another option. I think you can place your SQL query in a cell as a string then have a Macro run it. Any ideas how I could do this?

我有一个相当复杂的SQL查询与嵌套子查询。当我尝试在Microsoft Query中使用参数时,我说我可以在不能以图形方式表示的查询中使用参数。所以我需要另一种选择。我认为您可以将SQL查询作为字符串放在单元格中,然后让宏运行它。我有什么想法可以做到这一点?

Thanks

-Jesse

3 个解决方案

#1

Here's what I do to work around the limitations of Microsoft Query in Excel 2007:

以下是我在Excel 2007中解决Microsoft Query限制的方法:

A produce a dummy query (SELECT NULL AS Test, for example) in Microsoft Query and insert it into the worksheet.

A在Microsoft Query中生成一个虚拟查询(例如,SELECT NULL AS Test)并将其插入到工作表中。

Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.

右键单击MS Query刚刚插入的表,然后单击表 - >编辑外部数据属性。

Click on the Connection Properties button, then click the Definition tab.

单击“连接属性”按钮,然后单击“定义”选项卡。

In the Command Text section, write out or paste in the query that you want, using the usual '?' convention for parameters, then click OK.

在“命令文本”部分中,使用通常的“?”写出或粘贴所需的查询。参数约定,然后单击“确定”。

Click OK to exit the External Data Properties window.

单击“确定”退出“外部数据属性”窗口。

Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.

再次右键单击该表,然后选择Table-> Parameters以通常方式绑定参数。

The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.

这个想法绕过了MS Query提供的GUI,它有一些基本引擎没有的任意限制。

This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW on the SQL server and query against that.

这适用于许多复杂的查询,但不是全部。当我遇到MS Query完全拒绝消化的查询时,我要么重构查询(如果可行),要么在SQL服务器上创建一个VIEW并对其进行查询。

#2

Unfortunately the ? doesn't work for most of my queries and a lot of them are not necessarily suited to being turned into views.

不幸的是?不适用于我的大多数查询,其中很多不一定适合转换为视图。

The main alternative I use is getting a macro to return the code

我使用的主要替代方法是获取宏来返回代码

Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim server, Database As String
Dim Data as Worksheet

Set data = ThisWorkBook.Worksheets("data")

'rename field here and elsewhere to your variable eg SD or StartDate
Dim field as string

server = "servername"
Database = "database"

'set connection string

If Con.State <> 1 Then
 Con.ConnectionString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & Database & ";Integrated Security=SSPI;"


'this is just setting the connection time out to infinite
setcono:
Con.ConnectionTimeout = 0
Con.CommandTimeout = 0


'this is making sure it set the connection time out to infinite
If Con.ConnectionTimeout > 0 Then GoTo setcono
If Con.CommandTimeout > 0 Then GoTo setcono

Con.Open

Set oRS = New ADODB.Recordset
oRS.ActiveConnection = Con

field = Range("A2").value

oRS.Source = "YOUR SQL QUERY "
oRS.Source = oRS.Source & " WHERE field = '"  & field & "'"

oRS.Open
data.Range("A2").CopyFromRecordset oRS
End If
oRS.Close
Con.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not Con Is Nothing Then Set oCon = Nothing

I would love Microsoft to fix the bug where it returns errors for the more complex queries as I find it frustrating creating macros just for the sake of returning a simple dataset

我希望微软能够修复它为更复杂的查询返回错误的错误,因为我发现仅仅为了返回一个简单的数据集而创建宏令人沮丧

#3

Another way to solve this is to use stored procedures

解决此问题的另一种方法是使用存储过程

CREATE PROCEDURE [dbo].[yourprocedure] @DATEFROM DATETIME, @DATETO DATETIME
AS


SELECT Query 
where date >= @datefrom
and date <= @dateto

then on the table properties click Connection Properties button, then click the Definition tab. In the Command Text section:

然后在表属性上单击“连接属性”按钮,然后单击“定义”选项卡。在“命令文本”部分中:

EXEC yourprocedure @DATEFROM = ?, @DATETO = ?

and direct the ? to the cells you want

并直接?到你想要的细胞

#1