在 vba 中使用 ADODB 在 SQL 中命名变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12796339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Named variables in SQL using ADODB in vba
提问by John Faben
I have lots of SQL scripts, many of which use various different variables throughout, and I'd like to be able to drop the results directly into Excel. The hope is to do this as 'smoothly' as possible, so that when someone gives me a new SQL script (which may be relatively complicated), it is relatively clean to set up the spreadsheet that gathers its results.
我有很多 SQL 脚本，其中许多在整个过程中使用各种不同的变量，我希望能够将结果直接放入 Excel。希望尽可能“顺利”地做到这一点，以便当有人给我一个新的 SQL 脚本（可能相对复杂）时，设置收集其结果的电子表格相对干净。
Currently trying to get this working using ADODB Command objects parameters, but I can't even manage to get a very basic example to work. I have the following VBA:
目前正在尝试使用 ADODB 命令对象参数使其工作，但我什至无法获得一个非常基本的示例来工作。我有以下 VBA：
Dim oConnection As ADODB.Connection
Set oConnection = New ADODB.Connection
oConnection.ConnectionString = "MyConnectionString"
oConnection.Open
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = oConnection
Up to here is just setting up the connection, which seems to work fine.
到这里只是建立连接，这似乎工作正常。
cmd.CommandText = "DECLARE @DateID integer;" & _
"SELECT TOP 10 * FROM dbo.SomeRecords " & _
"WHERE DateID = @DateID"
cmd.CommandType = adCmdText
Dim DateID As ADODB.Parameter
Set DateID = cmd.CreateParameter("@DateID", adInteger, adParamInput)
cmd.Parameters.Append DateID
DateID.Value = 20120831
Dim rst AS ADODB.RecordSet
Set rst = cmd.Execute()
ActiveSheet.Range("A1").CopyFromRecordset rst
Unfortunately, this doesn't return anything. However, if I replace the line:
不幸的是，这不会返回任何内容。但是，如果我替换该行：
"WHERE DateID = @DateID"
with this:
有了这个：
"WHERE DateID = 20120831"
Then the query returns exactly what you'd expect (the top 10 records from August 31), so obviously I'm not passing the value of the variable from VBA into SQL properly, but I have to admit that I'm pretty much stuck.
然后查询完全返回您所期望的（8 月 31 日的前 10 条记录），所以显然我没有将变量的值从 VBA 正确传递到 SQL，但我不得不承认我几乎卡住了.
Certainly somethingis being passed into SQL (if I change the type of the variable @DateID to datetime in the SQL, then I get a SQL Server arithmetic overflow error, from trying to convert something to datetime), but it isn't doing what I was expecting.
肯定有东西被传递到 SQL（如果我在 SQL 中将变量 @DateID 的类型更改为日期时间，那么我会收到一个 SQL Server 算术溢出错误，尝试将某些内容转换为日期时间），但它并没有做什么我很期待。
I guess there are two questions: Is there a way to fix this code? Is there a better way of achieving the general goal described at the start?
我想有两个问题：有没有办法修复这段代码？有没有更好的方法来实现开头描述的总体目标？
采纳答案by RichardTheKiwi
Try this
尝试这个
cmd.CommandText = "DECLARE @DateID integer;" & _
"SET @DateID = ?DateID;" & _
"SELECT TOP 10 * FROM dbo.SomeRecords " & _
"WHERE DateID = @DateID"
.....
Set DateID = cmd.CreateParameter("?DateID", adInteger, adParamInput)
Re:
关于：
in which case why bother having names for them in the first place
在这种情况下，为什么首先要为它们命名
Well, so that you can match them up as shown above. By all means use it many times, but use a sql-server localdeclare and set it there as shown.
好吧，这样您就可以如上图所示将它们匹配起来。一定要多次使用它，但使用sql-server local声明并将其设置在那里，如图所示。
回答by podiluska
Remove the DECLARE @DateID integer;from the SQL string.
ie:
DECLARE @DateID integer;从 SQL 字符串中删除。IE：
cmd.CommandText = "SELECT TOP 10 * FROM dbo.SomeRecords " & _
"WHERE DateID = @DateID"
回答by Alex K.
Parameters in an ADO parameterized query want a ?as placeholder so:
ADO 参数化查询中的参数需要一个?作为占位符，因此：
"SELECT TOP 10 * FROM dbo.SomeRecords WHERE DateID = ?"
