Invoke-Sqlcmd: -Variable parameter does not allow to pass an empty string or a specify a value that contains an '='

[Matteo-T]

The following cmdlet does not work and I cannot think of a way to make it work.

Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1, '`$(V2)' AS C2" -TrustServerCertificate -ServerInstance localhost -Variable @('V1=1', 'V2=')

Result:

Invoke-Sqlcmd : The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the
'var=value' format for defining a new variable.
At line:1 char:1

Similarly, trying to pass a variable whose value happens to start (or end) with spaces is not possible:

# Note the leading space in the value of the V1 variable
# The desired output is 1 instead of 2!
(Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1" -TrustServerCertificate -ServerInstance SQLTOOLS2014-3 -Variable @('V1= A')).C1.Length

Similarly, trying to pass a variable whose value happens to be (or contain) an '=' character (which is common in tokens, base64 blobs, etc...)

# Note the leading space in the value of the V1 variable
# The desired output is "A=A", instead we get an error!
(Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1" -TrustServerCertificate -ServerInstance SQLTOOLS2014-3 -Variable @('V1=A=A')).C1

[Matteo-T]

This is fixed in v22.0.52.2+ (when available)