As I mention in the comments, this is much easier with something like the DbaTools module. Then you can use New-DbaSqlParameter to create add a table type parameter, built by using ConvertTo-DbaDataTable, and pass it to Invoke-DbaQuery.

Take the following overly simplified table:

CREATE TABLE dbo.StructuredTest (ID int IDENTITY,
                                 SomeString varchar(10),
                                 SomeInt int);
GO

INSERT INTO dbo.StructuredTest (SomeString,
                                SomeInt)
VALUES('abc',1),
      ('def',2),
      ('xyz',1);

Now we want to pass a list of IDs (ints). We would need a table type, such as:

CREATE TYPE dbo.Ints AS table(IntValue int);

Then we can use the following script from PowerShell to pass a table type as the type dbo.Ints:

#Define the initial query
$Query = 'SELECT * FROM dbo.StructuredTest ST WHERE EXISTS (SELECT 1 FROM @TableVariable TV WHERE TV.Intvalue = ST.ID);'

#Define an Array
$IDs = @()
#Add the rows to the array. According to the documentation, these should be PSCustomObjects
$IDs += [PSCustomObject]@{Intvalue = 1}
$IDs += [PSCustomObject]@{Intvalue = 2}
#Convert to a DataTable
$DT = ConvertTo-DbaDataTable -InputObject $IDs

#Create the parameter. TypeName is the TYPE in your database. TableVariable the name of the variable/parameter in your query (without the @)
$TableVariable = New-DbaSqlParameter -ParameterName TableVariable -SqlDbType Structured -Value $DT -TypeName 'dbo.Ints'

#Run the query, passing your (Table) Variable)
Invoke-DbaQuery -SqlInstance 'SandboxServer' -Database Sandbox -Query $Query -SqlParameter $TableVariable

If you wanted to do an INSERT against the table, then it would look something like this:

--Create TYPE in SQL Server
CREATE TYPE dbo.StructuredType AS table (SomeString varchar(10),
                                         SomeInt int);

#PoSh Script to INSERT data into SQL Server:
$Query = 'INSERT INTO dbo.StructuredTest (SomeString, SomeInt) SELECT SomeString, SomeInt FROM @TableVariable;'

$Rows = @()
$Rows += [PSCustomObject]@{SomeString = 'ghi'
                           SomeInt = 2}
$Rows += [PSCustomObject]@{SomeString = 'ace'
                           SomeInt = 3}
$DT = ConvertTo-DbaDataTable -InputObject $Rows
$TableVariable = New-DbaSqlParameter -ParameterName TableVariable -SqlDbType Structured -Value $DT -TypeName 'dbo.StructuredType'

Invoke-DbaQuery -SqlInstance 'SandboxServer' -Database Sandbox -Query $Query -SqlParameter $TableVariable

I fundamentally agree with this answer – using DbaTools would make your life easier – but here’s how you could do it programmatically from PowerShell without invoking any external tools:

$runIDs = @(1, 2, 3)

# establish connection
$connection = [System.Data.SqlClient.SqlConnection]::new("connection string goes here")
$connection.Open()

# define the first part of the command text
$cmdTextPreamble = @'
DECLARE @RunIDsToDelete TABLE (RunID INT);

INSERT INTO @RunIDsToDelete (RunID) 
VALUES 
'@

# create a command object
$cmd = [System.Data.SqlClient.SqlCommand]::new()
$cmd.Connection = $connection

# now construct the final command text, one insertion at a time
$cmdText = $cmdTextPreamble
for ($i = 0; $i -lt $runIDs.Count; $i++) {
  # calculate the next parameter name
  $paramName = "@runID${i}"
  # add the parameterized value set to the command text
  $cmdText += "(${paramName}),"
  # and bind the corresponding value to the parameter name
  $cmd.Parameters.AddWithValue($paramName, $runIDs[$i]) |Out-Null
}

# remove trailing `,` from the command text and update the $cmd object
$cmd.CommandText = $cmdText.TrimEnd(',')

# execute insert command
$rowCount = $cmd.ExecuteNonQuery()

Write-Host "Inserted ${rowCount} rows into table"

• sqlcmd.exe has its own, nonstandard double-quoting requirements with respect to -v arguments, which run afoul of how PowerShell builds the process command line for external-program calls behind the scenes.

  • In short, sqlcmd.exe needs to see a -v argument with selective double-quoting, namely around the value part only, e.g. FOO="bar" and FOO="bar baz", whereas PowerShell turns these arguments into FOO=bar and "FOO=bar baz" behind the scenes.

  • See GitHub issue #18554 for an extensive discussion.

• As a workaround, you can use PowerShell’s broken passing of arguments with embedded " characters (double quotes) to external programs. That is, you can use embedded " here and rely on their not getting escaped as " behind the scenes.

  • While the broken behavior is fixed in PowerShell (Core) 7 v7.3+ , where getting the old, broken behavior back now requires opt-in via $PSNativeCommandArgumentPassing = 'Legacy', this opt-in isn’t actually necessary for sqlcmd.exe, because it is on an exception list of known executables to which the old behavior continues to be applied automatically in the default $PSNativeCommandArgumentPassing mode, 'Windows' – see the docs for the complete list.

The following shows the solution as a direct invocation of your sqlcmd.exe command rather than creating a string representation of it:

$runIds = @(1, 2)
$paramList = $runIds.ForEach({ '(' + $_ + ')' }) -join ',' # -> "(1),(2)"

# Note the embedded, escaped " chars. around $paramList
sqlcmd -S sql1 -d mydb -i $sqlScriptPath -v IDs=`"$paramList`"

To get the command as a string representation, using an expandable (interpolating), double-quoted string ("..."), as in your own attempt, both the embedded " and ` themselves requires `-escaping:

$sqlCommand =
  "sqlcmd -S sql1 -d mydb -i `"$sqlScriptPath`" -v IDs=```"$paramList```""

However, note that there’s usually no reason to store a full command line in a string (typically for ill-advised use with Invoke-Expression), because PowerShell makes it easy to construct command lines programmatically that can be invoked directly – see this answer for more information, and this answer for how to pass arguments stored in an array.