Powershell: How to enclose an array of strings with quotes so that SQL "IN" operator works?

Question

I'm basically asking users to enter some formIds separated by a comma and passing that string to a function which does a SQL query update. The problem is I'm not able to use the IN operator since the query has to be in the format: "(''),('')".

I want to make this query work:

UPDATE [dbo].[Details] SET Field1 = 0 WHERE FormId in ('123','456','789')

And for this I want to accept this user input:

123,456,789

How can I get this to work?

Function Execute-Query([String[]] $FormIds)
{
$arr = $FormIds -split ','

ForEach ($formId in $arr)
{
    echo $formId
}
$dataSource = "x"
$user = "x"
$pwd = ""x
$database = "x"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
$sqlcmd.CommandText = “UPDATE [dbo].[Details] SET Field1 = 0 WHERE FormId in (????)”
$rowsAffected = $sqlcmd.ExecuteNonQuery()

$connection.Close()

}
$strFormIds = Read-Host "Enter FormIds:"
Execute-Query $strFormIds

Answer 1

Not sure how IN comes into play, but assuming you just want to construct a query string as in your example, use the following subexpression instead of ???? in your code:

$("'" + ($FormIds -join "','") + "'")

That said, your Read-Host call will not automatically convert your '123','456','789' user input into an array of individual strings - instead, it will create a single-element array whose only element is that very string as a whole.

You must therefore explicitly parse your single-string user input into an array first, as follows:

[string[]] $formIds = $strFormIds -split '[, ]' -ne ''

Note the set of characters to split by - , and <space> - so that both 123,456 and 123, 456 or even 123 456 would work. The -ne '' part filters out empty elements that result from multiple adjacent spaces, for instance.