How to pass an array to SQL query?

Question

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

TessellatingHeckler · Accepted Answer · 2014-03-20 03:41:24Z

2

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)

answered Mar 20, 2014 at 3:41

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adil Hindistan Over a year ago

Yes, it's a funny way of doing it but it works. Thank you! This is pretty much what I thought I could do as a last resort but was wondering if there is another way used in sql queries that I am not aware of. I will leave this for a while to see if anyone can come up with a different solution.

Collectives™ on Stack Overflow

How to pass an array to SQL query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related