How do I use results of PowerShell's 'invoke-SQLcmd' in an 'if' statement?

Question

I can't figure out how to get the results of the query (qty_records) which is 5 so I can use it in a PowerShell If statement.

====

$my_query = "select count(CustomerNumber) as qty_records from customers"

$qty_records = Invoke-Sqlcmd -Query $my_query -ServerInstance "2008c" -Username sa -Password abc.1234 -Database MikeDB

if ($qty_records -gt 4) {
    write-host "do something"
} else {
    Write-Host "do something else"
}

====== thanks

Answer 1

I think the problem here is that Invoke-SqlCmd returns a datarow even if it's only returning a single value, so you need to expose the actual content. It's been a while since I worked in SQL so I'm a bit fuzzy on how the return values get named but I am reasonably sure based on your SELECT that it will return with a .qty_records property, so you would need to modify your if statement like so

if ($qty_records.qty_records -gt 4) {
  write-host "do something"
} else {
  Write-Host "do something else"
}

Note that it could return as .CustomerNumber if I recall the mechanics incorrectly. If your interested in other methods of working with datarows I' recommend checking out This Post