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