How to append a nested, looped object to a WHERE Statement in Powershell-CodePudding

Basically I'm trying to take 5 SQL Queries, and run them against a list of hundreds of Object IDs, and then export the results across 5 Query named tabs inside an XLSX named after each Object ID. I'm so close but I just don't know how to add those Object IDs into each Query's WHERE clause. I get data if I break the loop and include $obj, and don't get data otherwise.

$SQLServer = "Servername"
    $SQLDBName = "Database"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"  #; User ID = $uid; Password = $pwd;
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 
 
   $SQL_Object_IDs = get-content "C:\PS\INPUT\Obj_IDs.txt"   
 
    $SQL_QUERY1 = 
    "Select [r_object_id] as 'Object ID'
    From [Database].[dbo].[table1]
    Where blah blah blah = ''"
    #$SQL_QUERY2 = Query2.SQL
    #$SQL_QUERY3 = Query3.SQL
    #$SQL_QUERY4 = Query4.SQL
    #$SQL_QUERY5 = Query5.SQL
 
    $SqlQueries = $SQL_QUERY1, =#,$SQL_QUERY2,$SQL_QUERY3,$SQL_QUERY4,$SQL_QUERY5
 
    $ExcelOutputPath = "C:\PS\OUTPUT\COLLATED_OBJ_IDs\"          
    $OutputExcelTabNames= "Obj_ID_Type1","Obj_ID_Type2","Obj_ID_Type3","Obj_ID_Type4","Obj_ID_Type5"          
    ForEach ($obj in $SQL_Object_IDs)
    {
   
            $SheetNo = 0
        
              ForEach ($SqlQuery in $SqlQueries)
              {
         
                    $DestinationPath = $ExcelOutputPath    "\"  $obj ".xlsx"
                    $SqlCmd.CommandText = $SqlQuery
                    $SqlCmd.Connection = $SqlConnection
                    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
                    $SqlAdapter.SelectCommand = $SqlCmd
                    $DataSet = New-Object System.Data.DataSet
                    $SqlAdapter.Fill($DataSet)
 
                    $DataSet.Tables[0] | Export-Excel -Path $DestinationPath -AutoSize -AutoFilter  -BoldTopRow -ClearSheet -WorksheetName $OutputExcelTabNames[$SheetNo]
                
                    $SheetNo  = 1
 
              }

CodePudding user response：

The safest way to pass arguments to a T-SQL query is by declaring and binding parameters:

$SQL_QUERY1 = 
"Select [r_object_id] as 'Object ID'
From [Database].[dbo].[table1]
Where columnName = @columnValue"

# ...

foreach($obj in $SQL_Object_IDs) {

    $sheetNo = 0

    foreach($SqlQuery in $SQLQueries) {
        $DestinationPath = $ExcelOutputPath    "\"  $obj  ".xlsx"
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection

        # clear any parameters from previous iterations
        $SqlCmd.Parameters.Clear()

        # bind appropriate value to parameter
        #SqlCmd.Parameters.AddWithValue('@columnValue', $obj)

        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet)
 
        $DataSet.Tables[0] | Export-Excel -Path $DestinationPath -AutoSize -AutoFilter  -BoldTopRow -ClearSheet -WorksheetName $OutputExcelTabNames[$SheetNo]
    }
}

The call to $SqlCmd.Parameters.AddWithValue('@columnValue', $obj) will replace the @columnValue placeholder in the query text with value stored in $obj.

If the Object ID values are numerical, then you'll want to convert $obj to [int] before binding the value:

# bind appropriate value to parameter
#SqlCmd.Parameters.AddWithValue('@columnValue', [int]$obj)