Passing multiple values to a SQL query IN statement in PowerShell-CodePudding

I've figured out how to run SQL queries with PowerShell and turn them into arrays I can work with. I've also figured out how to pass specific subsets of variables or arrays to the query. Like this:

$LastMonth = [DateTime]::Now#.AddMonths((-1))
# define target database and server
$TargetServer = 'server name'
$TargetDatabase = 'db name'

$ConnectionString = "Server="   $TargetServer   ";Database="   $TargetDatabase   ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();

$sql = "SELECT [AVAILABILITY_SAID_ID]
    ,[AVAILABILITY_MINUTES]
    FROM [dbo].[AVAILABILITY_MINUTES]
    where [AVAILABILITY_MONTH] = $($LastMonth.Month) and [AVAILABILITY_YEAR] = $($LastMonth.Year);"
# execute SQL command
$TargetCommand = New-Object System.Data.SqlClient.SqlCommand($sql, $TargetConnection);
$reader = $TargetCommand.ExecuteReader()
    $availability = @()
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i  )
        {
            $row[$reader.GetName($i)] = $reader.GetValue($i)
        }
        $availability  = new-object psobject -property $row            
    }
    $reader.Close()

What I can't figure out is how to do it as an IN statement with multiple items. I can get the list to be passed, but I can't figure out how to get the parentheses around it so that the query actually works.

$TargetServer = 'server name'
$TargetDatabase = 'db name'

$ConnectionString = "Server="   $TargetServer   ";Database="   $TargetDatabase   ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();

          $sql = "SELECT
            incidentid AS EVENT_LIFECYCLE_VALUE,
            Priority AS Priority_Code,
            keycode AS ASSET_KEY,
            Description AS EVENT_DATA,
            CreatedDateTime AS [Opened],
            ClosedDateTime AS [Closed]
            FROM incident
            WHERE keycode IN $("'"   ($said.Saidkeycode -join "','")   "'")
            AND Priority IN (1, 2)
            AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
            AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
            ORDER BY EVENT_LIFECYCLE_VALUE";
          $SourceCommand = New-Object System.Data.SqlClient.SqlCommand($sqlCmd, $SourceConnection);
          $reader = $SourceCommand.ExecuteReader()
          $incidents = @()
          while ($reader.Read())
          {
              $row = @{}
              for ($i = 0; $i -lt $reader.FieldCount; $i  )
              {
                  $row[$reader.GetName($i)] = $reader.GetValue($i)
              }
              $incidents  = new-object psobject -property $row            
          }
$reader.Close()

From what I've found, parametrisation is the answer, but I haven't been able to wrap my head around how to get it to actually work. I'm not sure if this is broken or if I just can't figure out how to get the results out of the dataset.

$TargetServer = 'server name'
$TargetDatabase = 'db name'

$ConnectionString = "Server="   $TargetServer   ";Database="   $TargetDatabase   ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
        $sqlCmd = $SourceConnection.CreateCommand()
        $sqlCmd.Connection = $SourceConnection
          $sql = "SELECT
            incidentid AS EVENT_LIFECYCLE_VALUE,
            Priority AS Priority_Code,
            keycode AS ASSET_KEY,
            Description AS EVENT_DATA,
            CreatedDateTime AS [Opened],
            ClosedDateTime AS [Closed]
            FROM Cherwell.dbo.Incident
            WHERE keycode IN (@SAID)
            AND Priority IN (1, 2)
            AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
            AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
            ORDER BY EVENT_LIFECYCLE_VALUE";
          $sqlCmd.CommandText = $sql
          $sqlCmd.Parameters.Add("@SAID", [Data.SQLDBType]::VarChar, 4).Value = $("'"   ($said.Saidkeycode -join "','")   "'")
          $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
          $dataSet = New-Object System.Data.DataSet
          $sqlAdapter.Fill($dataSet)

CodePudding user response：

There are a couple of options open to you here. I am going to take the code I used in your prior question, as I still had it open in my IDE.

For a delimited list, this would be simply splitting the value in your SQL. I assume you are on a (fully) supported version of SQL Server, as you make no mention that you aren't. This means you can simply use STRING_SPLIT:

$connectionString = 'Server="srvsql2019dev\Sandbox";Database=Sandbox;Trusted_Connection=true;'

$sqlConn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
try{
    $sqlConn.Open()
    $sqlCmd = $sqlConn.CreateCommand()
    $sqlCmd.Connection = $sqlConn
    $query = "SELECT [name]
    FROM sys.databases
    WHERE name IN (SELECT [value] FROM STRING_SPLIT(@Databases,','));" #IN using STRING_SPLIT
    $databases = Get-Content -Path "./DBNames.txt" #txt file containing database names.
    $sqlCmd.CommandText = $query
    $sqlCmd.Parameters.Add("@Databases", [System.Data.SqlDbType]::NVarChar,4000).Value = $databases -join "," # Pass the values as a comma delimited list. 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
}
finally{
    if ($sqlAdapter -ne $null) { $sqlAdapter.Dispose(); }
    if ($sqlConn -ne $null) { $sqlConn.Close(); }
}

$dataSet.Tables

Note that I define the parameter as 4000 characters long, not 4, or some other very short value; as this would result in truncation.

The alternative method, as I mentioned, is to use a table type parameter (TTP). To do this, you will first need to create the TYPE, and you would also need to create a stored procedure. As you are using an inline query here, then I won't cover this here. If, however, you are passing 100's of values in your IN, then moving to a TTP (with an INDEX) will likely be more performant.

Another method is with json. Json is easy to create in PS (ConvertTo-Json) and consume in a SQL query (OPENJSON). Json might be overkill for a simple delimited list but is easier than a TVP for multiple columns since one doesn't need to create a type.

CodePudding user response：

This may not be the best way to do this, but it's what I finally was able to get to work. I defined the properly punctuated array as a new variable $insaid, and used that new variable with the parentheses in the IN statement.

$TargetServer = 'server name'
$TargetDatabase = 'db name'

$ConnectionString = "Server="   $TargetServer   ";Database="   $TargetDatabase   ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();
$insaid = $("'"   ($said.Saidkeycode -join "','")   "'")
          $sql = "SELECT
            incidentid AS EVENT_LIFECYCLE_VALUE,
            Priority AS Priority_Code,
            keycode AS ASSET_KEY,
            Description AS EVENT_DATA,
            CreatedDateTime AS [Opened],
            ClosedDateTime AS [Closed]
            FROM incident
            WHERE keycode IN ($insaid)
            AND Priority IN (1, 2)
            AND CONVERT (date, CreatedDateTime) <= DATEADD(DAY, -2, CONVERT (date, GETDATE()))
            AND CreatedDateTime >= DATEADD(DAY, -90, GETDATE())
            ORDER BY EVENT_LIFECYCLE_VALUE";
          $SourceCommand = New-Object System.Data.SqlClient.SqlCommand($sqlCmd, $SourceConnection);
          $reader = $SourceCommand.ExecuteReader()
          $incidents = @()
          while ($reader.Read())
          {
              $row = @{}
              for ($i = 0; $i -lt $reader.FieldCount; $i  )
              {
                  $row[$reader.GetName($i)] = $reader.GetValue($i)
              }
              $incidents  = new-object psobject -property $row            
          }
$reader.Close()