Error passing non-literal scripting variables to Invoke-Sqlcmd in PowerShell

Question

I am trying to pass in some scripting variables to Invoke-Sqlcmd in PowerShell like so:

$hello = "hello"
$params = "greeting=" + $hello, "audience=world"
Invoke-Sqlcmd -Query "select '`$(greeting) `$(audience)'" -Variable $params

I get the following error:

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.

But I am successful if I remove $hello and use a literal:

$params = "greeting=hello", "audience=world"

.GetType() returns the same thing for both versions of $params, so I'm unsure what the issue is.

Answer 1

On your first example, the variable $params is being set to string:

$hello = "hello"
$params = "greeting=" + $hello, "audience=world"
$params.GetType()

IsPublic IsSerial Name          BaseType
-------- -------- ----          --------
True     True     String        System.Object

PS /> $params
greeting=hello audience=world

Unless you tell PowerShell you want an object[] as result of your operation. i.e.: surrounding the concatenation operation with ( ):

$params = ("greeting=" + $hello), "audience=world"
$params.GetType()

IsPublic IsSerial Name            BaseType
-------- -------- ----            --------
True     True     Object[]        System.Array

PS /> $params
greeting=hello
audience=world

Or using the array sub-expression operator for example:

$params = @(
    "greeting=" + $hello
    "audience=world"
)

---

For official documentation on this, see about_Operator_Precedence.

$string = 'a'
$array = 'b','c'

PS /> ($string + $array).GetType()

IsPublic IsSerial Name          BaseType
-------- -------- ----          --------
True     True     String        System.Object

PS /> $string + $array
ab c

PS /> ($array + $string).GetType()

IsPublic IsSerial Name            BaseType
-------- -------- ----            --------
True     True     Object[]        System.Array

PS /> $array + $string
b
c
a