[SQL] Passing an array as a parameter from PowerShell to an SQL script

I'm creating a quite complex SQL script to delete from a database a bunch of records. Because I have to do a lot of operations, I want to save the IDs of the records I have to delete in a temporary table

DECLARE @RunIDsToDelete TABLE (RunID INT);

INSERT INTO @RunIDsToDelete (RunID)
VALUES
N'$(runIDs)'


The list of IDs to delete - alias to save in the table - must to be a parameter from the PowerShell script. The SQL script has to send back to the PowerShell script the confirmation that I want to read.

$runIds = @(1, 2)
$paramList = $runIds -join ","

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


My first attempt was to prepare the list and pass the result string to the SQL script but I get this error

sqlcmd : Sqlcmd: ',(2),(4)': Invalid argument. Enter '-?' for help.

+ CategoryInfo : NotSpecified: (Sqlcmd: ',(2),(... '-?' for help.:String) [],
RemoteException
+ FullyQualifiedErrorId : NativeCommandError​

Then, I tried to pass a string like that

$sqlCommand = "sqlcmd -S sql1 -d myDb -i $($sqlScriptPath) -v IDs=""(1), (2)"""


but again I get the same error. How can I pass the list of IDs then?

Continue reading...