Can't access output variable in command text

Question

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = db
    .CommandText = "SELECT @date = '2019-01-01'"
    .Parameters.Append(.CreateParameter("@date", adDBDate, adParamOutput))
    .Execute
End With

Gives...

Must declare the scalar variable "@date".

Why can't I access the output parameter in the query text?

Answer 1

Named parameters work as expected on both sides (Server: SQL Server, Client: ADODB & VBScript for your case) only if the provider supports it. For SQL Server providers it is supported only with commands configured to call a stored procedure with named parameters (where cmd.CommandType set adCmdStoredProc and cmd.NamedParameters set True).

For an ordinary command like yours, named parameters are not recognized by the server, only the ? placeholders recognized as parameters in the query.

So you should try something like the following.

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = db
    .CommandText = "SELECT ? = '2019-01-01'"
    .Parameters.Append(.CreateParameter("@dummyForServerNotForClient", adDBDate, adParamOutput))
    .Execute
    ' must print: 2019-01-01
    Response.Write .Parameters("@dummyForServerNotForClient").Value
End With

Since the parameter names ignored by servers, you can write the same code by omitting the parameter name, and access the parameter's value by using its ordinal position in the collection. IMHO, with the lack of explicitly named parameters the code becomes more logical and readable.

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = db
    .CommandText = "SELECT ? = '2019-01-01'"
    .Parameters.Append(.CreateParameter(, adDBDate, adParamOutput))
    .Execute
    ' also must print: 2019-01-01
    Response.Write .Parameters(0).Value
End With

I hope this helps you to understand the concept.