Where must variables in PowerShell here-strings be initialized?

I have a rather complex SQL query I want to use in a here-string in a PowerShell script. Inside the query, I want to replace certain values with PowerShell variables. I'm trying to understand exactly where I need to declare and initialize the variables that will be used in the here-string.

Simplified example:

$SqlQuery = @"
update MyTable 
set foo = $bar
where baz = $quux
"@

...

foreach ($part in $parts) {
    $bar = $part[0]
    $quux = $part[1]

    Run-SqlNonQuery $SqlQuery    #Run-SqlNonQuery is a funct. that executes the query
}

I'm declaring the here-string at the top, and it references a couple of PowerShell variables, $bar and $quux, that change each time thru the loop.

However, I'm getting errors when I run my script. When I run a SQL trace, there's no value where the PowerS开发者_如何学Pythonhell variables appear in the PS script.

Do the PowerShell variables get interpolated when called in a here-string like this? Do I need to put the entire here-string inside the loop where the variables are initialized? What's the right structure for something like this?

I'm running PowerShell on Windows 7, .Net 4, VS2010. I'm not clear on the version; $Host returns

Name             : PowerConsole
Version          : 1.0.30222.0

but $PSVersionTable returns

PSVersion             2.0                                                      
PSCompatibleVersions  {1.0, 2.0}
BuildVersion          6.1.7601.17514

edited to include Mark's suggestion assumes you match the order of the array in part to the order used with {x} etc..

$SqlQuery = @"
update MyTable 
set foo = {0}
where baz = {1}
"@

...

foreach ($part in $parts) {
    Run-SqlNonQuery $SqlQuery -f $part    #Run-SqlNonQuery is a funct. that executes the query
}

Original version

this should do the job

$SqlQuery = @"
update MyTable 
set foo = {0}
where baz = {1}
"@

...

foreach ($part in $parts) {
    $bar = $part[0]
    $quux = $part[1]

    Run-SqlNonQuery $SqlQuery -f $bar, $quux     #Run-SqlNonQuery is a funct. that executes the query
}

If you want more info Google "powershell string formatting"

HTH, Matt

Try this (single quoted here-string):

$SqlQuery = @'
    update MyTable 
    set foo = $bar 
    where baz = $quux
'@

foreach ($part in $parts) 
{
    $bar = $part[0]
    $quux = $part[1] 
    $q = $ExecutionContext.InvokeCommand.ExpandString($SqlQuery)
    Write-Host $q
    #Run-SqlNonQuery $q
}

Generally you need you declare the here-string after you assign the variables (inside the loop). You can use a sub-expression to expand the values (e.g $(...)):

foreach ($part in $parts) {

$SqlQuery = @"
    update MyTable 
    set foo = $bar 
    where baz = $quux

"@

$bar = $part[0]
$quux = $part[1]    

Write-Host $($SqlQuery)
#Run-SqlNonQuery $($SqlQuery)
}

As Shay notes, the variables in the here-string need to be declared before they're used in the here-string. I've had occasion to want to move it just to make the script easier to read / maintain, and used a script block:

$MakeHereString = {
@"

Variables 1-3 are:
Variable 1 = $variable1
Variable 2 = $variable2
Variable 3 = $variable3
"@
}

$Variable1 = "First"
$Variable2 = "Second"
$Variable3 = "Third"

&$MakeHereString

Variables 1-3 are:
Variable 1 = First
Variable 2 = Second
Variable 3 = Third

For your example:

$SqlQuery = {
@"
update MyTable 
set foo = $bar
where baz = $quux
"@
}
...

foreach ($part in $parts) {
    $bar = $part[0]
    $quux = $part[1]

    Run-SqlNonQuery (&$SqlQuery)    #Run-SqlNonQuery is a funct. that    executes the query
}