CODEWITHSUNDEEP
sqlpowershell
Adil Hindistan
Adil Hindistan

Reputation: 6615

How to pass an array to SQL query?

I am trying to create a sql query and having a bit of difficulty in using 'operator'

See below array is kinda splat in the query string and only the first one makes it.

c:\>"select * from mytable where kerberosID in ('{0}')" -f @('a','b','c')
select * from mytable where kerberosID in ('a')

My desired SQL query would be: 

select * from mytable where kerberosID in ('a','b','c')

What would be the best way to pass that array into the query string?

Actual code snippet

    param ( 
            [string[]][Alias('host')]$ComputerName, 
            [string[]]$kerberosid 
          )


    [System.Data.SqlClient.SqlConnection]$sqlConnection = Open-DatabaseConnection

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlConnection

    ### This is the line where I have the above query
    $sqlCmd.CommandText = "SELECT * from dbo.vLogon where KerberosID in ('{0}')" -f @($kerberosid)

P.S. A similar issue is mentioned here, though for a different purpose: http://connect.microsoft.com/PowerShell/feedback/details/518276/f-format-operator-bug-when-right-side-value-is-an-array-variable

Upvotes: 0

Views: 1934

Answers (1)

TessellatingHeckler
TessellatingHeckler

Reputation: 29003

The linked code is formatting the array as a string first, using a bit of a curious join, then inserting that string into the SQL query as a single thing.

e.g. for your example:

c:\> "select * from mytable where kerberosID in ('{0}')" -f (@('a','b','c') -join "', '")
select * from mytable where kerberosID in ('a', 'b', 'c')

because

c:\> @('a','b','c') -join "', '"
a', 'b', 'c

# note how it's almost array notation, but is 
# missing the start and end single quotes (which are in the other string)

Upvotes: 2

Related Questions