SQL in Excel: Cannot call declared variable

I am trying to breakdown my SQL string in Excel VBA, storing my variables in the first part of the SQL, and calling them in the second part. It seems that declared variables is not my friend here?

First part(Declaring my variable)

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim sSQL As String

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly
Set cnn = New ADODB.Connection
cnn.CommandTimeout = GCL_dbTimeout

cnn.Open "XXX"
rst.ActiveConnection = cnn

sSQL = "DECLARE @Id AS INT = 1234 ; SELECT @Id AS [Id] INTO #cte_TEMP;"
cnn.Execute (sSQL)

Second part(Calling my variable)

Using a temp table works:

rst.Open "SELECT * FROM #cte_TEMP;"

But calling the variable doesn’t:

rst.Open "SELECT @Id;"

Product of the Month September 2016

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *