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;"

Answer

In-memory @variables are only in-scope during the execution of a single command; once rst.Open returns, the variable is gone and no longer exists.

By storing it into a #temp table, you are persisting the value to physical disk storage, which leaves it available to other subsequent commands.

Don’t forget to DROP TABLE #cte_TEMP; at one point! 🙂

Note: #temp tables are only accessible from the user that created it. If you need to access it from a different connection string, you need to use ##temp tables instead.