How to use PowerShell Invoke-Sqlcmd to query and table and update a PowerShell $variable?

Question

How do I update a PowerShell variable using an Invoke-Sqlcmd statement?

Eg.

$PSVariable
Import-Module sqlserver
$QueryStatement = "SELECT $PSVariable = Max(Data) FROM MyDatabase"
 
Invoke-Sqlcmd -ServerInstance "MyServer\Instance" -Query $QueryStatement

Not sure what is off with this code. I am new to the PowerShell sqlserver module

Answer 1

give this a go...

$Query = "SELECT Max(Data) as MaxData FROM MyDatabase"
$Result = Invoke-Sqlcmd -ServerInstance "MyServer\Instance" -Query $Query | select -expand MaxData 
write-host $Result

• Kefash: This did answer my question. Is there a way I can store my result to a SQL variable like Declare @MyVariable int; then update that and return the result?