Powershell Invoke-Sqlcmd Use result in another query

Question

I'm attempting to use a result returned from Invoke-Sqlcmd $ORDUNIQ as a variable in a second query. However the second $query2 returns 0 records. Substituting the variable for the actual string value '35973189' works fine. I'm assuming that $ORDUNIQ.ORDUNIQ is the wrong way to use this?, although running $ORDUNIQ.ORDUNIQ on the command line does return '35973189' correctly

Here is my code

$RAWFiles = Get-ChildItem  D:\Imports\Test\*.csv
$content = Import-Csv $RAWFiles | Where-Object 'Order Type' -eq 'B2B'
ForEach ($row in $content) {
$ORDNUMBER = $row.'OrderRef'
$SQLServer = "XXXXX-XXX"
$db1 = "XXXXXX"
$query1 = "SELECT ORDUNIQ FROM OEORDH WHERE ORDNUMBER = '$ORDNUMBER'"
$query2 = "SELECT OEORDD.ITEM FROM OEORDD WHERE ORDUNIQ = '$ORDUNIQ.ORDUNIQ'"
$ORDUNIQ = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $query1 
$ORDDATA = Invoke-SQLcmd -ServerInstance $SQLServer -Database $db1 -Query $query2
}

First -- by the time `$query2` is assigned `$ORDUNIQ` has no value yet, you need to move that assignment to after the first `Invoke-SqlCmd`. `Invoke-SqlCmd` does not support parameters, you're passing literal strings around and PowerShell is substituting in the value. This is also why things will break if you need to pass `NULL`, a value with a single quote in it, a `DateTime` value, and so on and so forth. Second -- to pass something more complicated than a single variable, you need `$()` to delimit the expression, so `$($ORDUNIQ.ORDUNIQ)`. — Jeroen Mostert, Jan 26 '23 at 13:09

Powershell Invoke-Sqlcmd Use result in another query

0 Answers0