How to pass array as variable in SQLCMD

Question

I am trying to pass server names in sql script , but its not working. Please help

SQL Script patch_report.sql, I am running via powershell giving error

SELECT * from table where server in ('$(trimsqlstr)')

Error

Msg 102,level Level 15, State 1, Server DBserver, Line 1
Incorrect syntax near 'server1'.

$DB_server = 'DBserver'
$serverName = "server1
server2
server3
"
$serverName = $serverName -split "\n" | foreach {$_.ToString().TrimEnd()}
$trimsqlstr = foreach($server in $serverName){
if ($serverName.Indexof($server) -eq $($serverName.Length-1)){
"'$Server'"
} else {
"'$Server',"
}

SQLCMD.exe -v trimsqlstr = "$($trimsqlstr)" -E -S $DB_server -W -i patch_report.sql

I am expecting it to result like this

SELECT * from table where server in ('server1','server2','server3')

Answer 1

The accepted answer is an excellent solution, I just want to point you towards the -join operator.

The -join operator is very practical for joining members of an array to a string.

You can do something like this:

$serverNames = "server1
server2
server3
"

$serverNameArray = $serverNames -split "\n" | foreach {$_.ToString().TrimEnd()} | Where-Object {$_} | foreach {"'$_'"}
$whereClause = $serverNameArray -join ','
$selectQuery = "SELECT * from table where server in ($whereClause)"

Where-Object {$_} is removing the empty elements.