Powershell SqlCmd - Using result values without defining variables

sql variables powershell literals sqlcmd
KickinMhl picture KickinMhl · Jun 12, 2013 · Viewed 16.5k times · Source

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?

Answer

Mike Shepard picture Mike Shepard · Jun 12, 2013

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

Related questions

How to declare variable and use it in the same Oracle SQL script?

I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as: DEFINE stupidvar = 'stupidvarcontent'; SELECT stupiddata FROM stupidtable WHERE stupidcolumn = &stupidvar; How can I declare a variable …

Read More
MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

Read More
How do I use variables in Oracle SQL Developer?

Below is an example of using variables in SQL Server 2000. DECLARE @EmpIDVar INT SET @EmpIDVar = 1234 SELECT * FROM Employees WHERE EmployeeID = @EmpIDVar I want to do the exact same thing in Oracle using SQL Developer without additional complexity. It seems like …

Read More