Passing multiple values to a SQL query IN statement in PowerShell

Question

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)

Passing a scalar variable is just that, A scalar value. IN ('a,b,c,d') would be the same as = 'a,b,c,d' not IN ('a','b','c','d'). You would either need to use a table type parameter, or split the value in your SQL. I will,. however, note that it's unliekly your delimited values will fit in a varchar(4). — Thom A
– Thom A ♦, Commented Jun 10, 2022 at 9:41
@Larnu I get a syntax error if the values do not have () around them. I'm using varchar(4) as that is the value of the column of the SQL table. Should I be using something else. They are all 4 letters long. — mouseskowitz
– mouseskowitz, Commented Jun 10, 2022 at 9:52
If you have a delimited value, how to you propose that more than one 4 character value fits in the varchar(4)? 'abcd,wxyz' as a varchar(4) is 'abcd'. — Thom A
– Thom A ♦, Commented Jun 10, 2022 at 9:55
I need my IN statement to be ('abcd', 'ewoh', 'avpe', 'awep'). So each item that it is searching for would be a varchar(4), correct? — mouseskowitz
– mouseskowitz, Commented Jun 10, 2022 at 10:00
As I stated, @SAID is a scalar variable; it cannot contain tuples; "You would either need to use a table type parameter, or split the value in your SQL.". — Thom A
– Thom A ♦, Commented Jun 10, 2022 at 10:00

Dan Guzman · Accepted Answer · 2022-06-10 12:40:57Z

0

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.

edited Jun 10, 2022 at 12:40

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

answered Jun 10, 2022 at 10:25

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

3 Comments

Dan Guzman Over a year ago

Adding 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.

Thom A Over a year ago

Good point, @DanGuzman . Would be worth posting your own answer, in my opinion.

mouseskowitz Over a year ago

It will be between 50 and 75 four letter strings. I guess we're making progress. I now get this error

Exception calling "Fill" with "1" argument(s): "Failed to convert parameter value from a Object[] to a String." (Line[266] Code[    $SqlAdapter.Fill($DataSet)])

I'm assuming that is the data being returned from the query?

mouseskowitz · Accepted Answer · 2022-06-14 08:52:56Z

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()

Collectives™ on Stack Overflow

Passing multiple values to a SQL query IN statement in PowerShell

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related