Dynamic SQL Where condition, in Powershell script

Question

I am working on a Powershell script where output of query #1 is where condition feed for query #2 but it is not getting the feed, if someone please see and let me know what could be possible solution s for this.

Also please note, in real environment, both the queries are being run on different instances and no possibilities of linked server

Below example is what I was trying in the AdventureWorks database:

$instance="WIN2016-SQL01\SQLSERVER_01"
$database = "AdventureWorks2014"
$query1 = "SELECT TOP 10 [BusinessEntityID]   FROM [AdventureWorks2014].[Person].[BusinessEntityAddress] where BusinessEntityID < 10 order by 1 "
$Q1 = (invoke-sqlcmd -query $query1 -ServerInstance $instance -Database $database)

$query2 = "SELECT *   FROM [AdventureWorks2014].[Person].[Person] where BusinessEntityID in ($Q1)"
$Q2 = invoke-sqlcmd -query $query2 -ServerInstance $instance -Database $database

Examine the $query2 value and you'll see the $Q1 value is becomes "System.Data.DataRow System.Data.DataRow...". You'll need to use a different technique to provide a list of values. Add the SQL Server version you are using to your question so you can get an answer most appropriate for your situation. — Dan Guzman
– Dan Guzman, Commented Jan 18, 2020 at 17:40

David Browne - Microsoft · Accepted Answer · 2020-01-18 20:13:26Z

2

Or you can just build a delimited string to substitute for your IN clause:

$instance="localhost"
$database = "AdventureWorks2017"
$query1 = "SELECT TOP 10 [BusinessEntityID] FROM [Person].[BusinessEntityAddress] where BusinessEntityID < 10 order by 1 "
$Q1 = (invoke-sqlcmd -query $query1 -ServerInstance $instance -Database $database)

$ids = ""
foreach ($r in $Q1)
{
  $ids += ","  + $r.BusinessEntityID
}

$ids = $ids.Substring(1)

$query2 = "SELECT * FROM [Person].[Person] where BusinessEntityID in ($ids)"
$Q2 = invoke-sqlcmd -query $query2 -ServerInstance $instance -Database $database 

$Q2 | format-table

answered Jan 18, 2020 at 20:13

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

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

2 Comments

SKumar Over a year ago

Thanks, Straight forward and yet simple. It worked.

mklement0 Over a year ago

Or, even simpler: $ids = $Q1.BusinessEntityID -join ','

Ian Kemp - SO dead by AI greed · Accepted Answer · 2020-01-18 19:53:26Z

The list of values can be passed as an XML parameter to the query, where XML methods can be used to extract the value. A JSON string value is an option in SQL 2016 and later but I see you are on SQL Server 2014.

The example below converts the Q1 result list of BusinessEntityID values to an XML parameter value. Since Invoke-SqlCmd doesn't support parameterized queries, it is necessary to use the SqlClient objects directly. An alternative to Invoke-SqlCmd is Invoke-DbaQuery from dbatools, which supports parameterized queries if you have that avaiable.

$instance="WIN2016-SQL01\SQLSERVER_01"
$database = "AdventureWorks2014"
$query1 = "SELECT TOP 10 [BusinessEntityID]   FROM [AdventureWorks2014].[Person].[BusinessEntityAddress] where BusinessEntityID < 10 order by 1 "
$Q1 = (invoke-sqlcmd -query $query1 -ServerInstance $instance -Database $database)

$list = @()
foreach ($row in $Q1)
{   
    $list += $row["BusinessEntityID"]
}
$listXml = $list | ConvertTo-Xml -NoTypeInformation
$listXmlString = $x.Objects.InnerXml

$query2 = "SELECT * 
FROM [AdventureWorks2014].[Person].[Person] 
WHERE BusinessEntityID IN (
    SELECT item.value('.','int')
    FROM @list.nodes('/Object') AS list(item)
    );"

$connectionString = "Data Source=$instance;Initial Catalog=$database;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($query2, $connection)
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
($command.Parameters.Add("@list", [System.Data.SqlDbType]::Xml)).Value = $listXmlString
$Q2 = New-Object System.Data.DataTable
$dataAdapter.Fill($Q2)

Collectives™ on Stack Overflow

Dynamic SQL Where condition, in Powershell script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related