CODEWITHSUNDEEP
sql-serverpowershellsql-scripts
SKumar
SKumar

Reputation: 59

Dynamic SQL Where condition, in Powershell script

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

Upvotes: 0

Views: 2681

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89236

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

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46223

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)

Upvotes: 0

Related Questions