Named variables in SQL using ADODB in vba

Question

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.

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:

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.

Certainly something is 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.

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?

Answer 1

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 local declare and set it there as shown.

Answer 2

Remove the DECLARE @DateID integer; from the SQL string. ie:

cmd.CommandText = "SELECT TOP 10 * FROM dbo.SomeRecords " & _ 
    "WHERE DateID = @DateID"  

Answer 3

Parameters in an ADO parameterized query want a ? as placeholder so:

"SELECT TOP 10 * FROM dbo.SomeRecords WHERE DateID = ?"