Powershell SqlCmd - Using result values without defining variables

Question

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?

Mike Shepard · Accepted Answer · 2013-06-12 18:49:08Z

9

You need to use $($Item.ID) in your double-quoted string, and it will work.

answered Jun 12, 2013 at 18:49

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell SqlCmd - Using result values without defining variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related