I am wondering if there is any way to use values for a dataset in a subsequent query.
Here is an example:
Works:
$Result = Invoke-Sqlcmd -query "SELECT Id, FirstName, LastName FROM Person"
foreach($item in $Result){
$ID= $item.Id
$SaleResults = Invoke-Sqlcmd -query "SELECT * FROM Sales WHERE Salesperson = $ID"
}
Does NOT Work:
foreach($item in $Result){
$SaleResults = Invoke-Sqlcmd -query "SELECT * FROM Sales WHERE Salesperson=$Item.Id"
}
The second example fails because it is using $Item.Id as a string rather that uniqueidentifier, hence the error: "Conversion failed when converting from a character string to uniqueidentifier."
Any idea how to use these values with their correct type, without creating a separate variable?
The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. The commands supported are Transact-SQL statements and the subset of the XQuery syntax that is supported by the database engine.
SQL PowerShell cmdlets can be used to manage instances of Azure SQL Database, Azure Synapse Analytics, and all supported SQL Server products.
You need to use $($Item.ID) in your double-quoted string, and it will work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With