I would like to declare a here-string outside of a loop use that string in the loop where the variables get resolved.
My ideal scenario would look like below. This doesn't work as Powershell evaluates the string one time before entering the loop instead of each time inside the loop kind of obvious but bitten by it nevertheless.
$number = "Number $($_)"
1..2 | % { $number }
I know I can use one of these solutions
1..2 | % { "Number $($_)" }
$number = "Number {0}"
1..2 | % { $number -f $_ }
$number = "Number <replace>"
1..2 | % { $number -replace "<replace>", "$_" }
but they have drawbacks I'd like to avoid
Due to the size of the string, declaring it inside the loop obfuscates the logic of the loop making the code less readable.
The formatting solution is too easy to get wrong when many variables are involved.
In the replace solution it's easier to match what get's replaced by what variable but I would have to chain many replace
commands.
Edit
Rereading my own question makes it obvious that the actual use case is missing from the question.
Note that ultimately I ended up choosing the formatting option
Following would declare the template with some variables that need replacing in a loop
$sqltemplate = @"
SELECT aud.dpt_mov_hex||aud.dpt_ref||aud.can_typ||TO_CHAR(aud.dte_aud-1,'YYYYMMDD')||'000001' transaction_id,
acc.dos_nbr contract_id, acc.pay_acc_nbr account_id,
CASE WHEN NULL IS NULL THEN unt.nam_unt ELSE unt.nam_unt||'<'||NULL ||'>' END product_id,
aud.dpt_ref, aud.dpt_mov_hex, aud.dpt_mov_dte uitwerkingsdatum,
CASE WHEN can_typ = 0 THEN 'VZ'||aud.dpt_mov_ven_typ ELSE 'VZ'||aud.dpt_mov_ven_typ||'-CR' END transactietype,
aud.dpt_mov_amt_eur bedrag_in_eur, aud.dte_cnv, aud.dpt_mov_fix_eur, aud.dpt_mov_con_inc, aud.dpt_mov_amt_sgn bedrag_teken,
aud.dpt_mov_amt_unt bedrag_in_units, aud.dpt_mov_amt_rte, aud.dpt_mov_amt_val_pre, aud.dpt_mov_amt_val_aft,
aud.dpt_mov_amt_ioc, aud.dte_exe verwerkingsdatum, aud.exe_mng, aud.cmt, aud.trn_nbr, aud.dte_aud datum_aanlevering, aud.can_typ
FROM lfe_dpt_mov_aud aud, vnv_isr_pay_acc acc, vnv_bel_unt unt
WHERE aud.dte_aud >= TO_DATE('$((Get-Date).ToString('dd.MM.yyyy'))', 'DD.MM.YYYY')
AND aud.dpt_ref = '{0}'
AND acc.pay_acc_nbr = '{1}'
AND unt.inv_unt = '{2}'
UNION
SELECT aud.dpt_mov_hex||aud.dpt_ref||aud.can_typ||TO_CHAR(aud.dte_aud-1,'YYYYMMDD')||'000001' transaction_id,
acc.dos_nbr contract_id, acc.pay_acc_nbr account_id,
CASE WHEN itr_rte IS NULL THEN unt.nam_unt ELSE unt.nam_unt||'<'||itr_rte ||'>' END product_id,
aud.dpt_ref, aud.dpt_mov_hex, aud.dpt_mov_dte uitwerkingsdatum,
CASE WHEN can_typ = 0 THEN 'VZ'||aud.dpt_mov_ven_typ ELSE 'VZ'||aud.dpt_mov_ven_typ||'-CR' END transactietype,
aud.dpt_mov_amt_eur bedrag_in_eur, aud.dte_cnv, aud.dpt_mov_fix_eur, aud.dpt_mov_con_inc, aud.dpt_mov_amt_sgn bedrag_teken,
aud.dpt_mov_amt_unt bedrag_in_units, aud.dpt_mov_amt_rte, aud.dpt_mov_amt_val_pre, aud.dpt_mov_amt_val_aft,
aud.dpt_mov_amt_ioc, aud.dte_exe verwerkingsdatum, aud.exe_mng, aud.cmt, aud.trn_nbr, aud.dte_aud datum_aanlevering, aud.can_typ
FROM lfe_dpt_mov_aud aud, vnv_dpt dpt, vnv_isr_pay_acc acc, vnv_bel_unt unt
WHERE aud.dpt_ref = dpt.dpt_ref
AND dpt.pay_acc = acc.pay_acc_nbr
AND dpt.inv_unt = unt.inv_unt
AND aud.dte_aud >= TO_DATE('$((Get-Date).ToString('dd.MM.yyyy'))', 'DD.MM.YYYY')
AND acc.pay_acc_nbr = '{1}'
AND unt.inv_unt = '{2}'
UNION
"@
and this template would get used in a statement such as this
$rolledbackMatchs is an array of custom object containing the three properties: dtp_ref, pay_acc_nbr and inv_unt.
$rolledbackMatches | ForEach-Object { $sqltemplate -f $_.dpt_ref, $_.pay_acc_nbr, $_.inv_unt }
Couple of approaches come to mind:
# loop.variables.ps1
$myVar = @"
Stuff going on with $_ in here
"@
and then in the loop itself:
1..2 | % { . .\loop.variables.ps1; <# do stuff with $myVar here #> }
$hereString = @'
Stuff (not yet) going on with $_ in here
'@
1..2 | % { $myVar = $ExecutionContext.InvokeCommand.ExpandString($hereString) }
$stringBlock = {
@"
Stuff going on with $_ in here
"@
}
1..2 | % { $myVar = &$stringBlock}