How to query from SQL comparing with array members?

Question

So I have an array containing computer names called hostnames[].

Contains: compname1, compname2, compname3 etc.

Actually it gets it's members from another SQL query.

I have a data table and I need to query all rows where hostname column has any of the computer names in my array.

something like:

select * from table where hostname in hostnames[]

How should I proceed to achieve my goal?

EDIT: I was thinking on the below:

string temp = "'" + hostnames[0] + "'";
for(int i=1; i<hostnames[].Lenght; i++)
{
temp = temp + ",'" + hostnames[i] + "'";
}
string query = "SELECT * FROM table WHERE hostname IN (" + temp + ")";

Here's an article for you: Parameterize a query containing an IN clause — Paul Sasik
– Paul Sasik, Commented Feb 22, 2013 at 17:41
Based on your tags, I'm not sure if you're doing this in C# or T-SQL. Can you clarify, please? — Melanie
– Melanie, Commented Feb 22, 2013 at 17:42
I don't have time to write an answer now, but If you are using SQL-Server 2008 or later PLEASE USE TABLE VALUED PARAMETERS. It is so much cleaner on the SQL side rather than using LIKE on a delimited string as is usually suggested. — GarethD
– GarethD, Commented Feb 22, 2013 at 17:58
@GarethD - Cool. I've never even heard of table valued parameters before - that's going to have a pretty profound effect on my coding technique going forward. — JDB
– JDB, Commented Feb 22, 2013 at 18:41

GarethD · Accepted Answer · 2013-02-25 13:14:34Z

The best way to use parameters for an IN clause is to use Table-valued parameters, in your case you will need a type as a table with one nvarchar column. I've used a generic name so the type can be reused without confusion:

CREATE TYPE dbo.StringList AS TABLE (value NVARCHAR(MAX));

Then it is simply a case of adding your values to a DataTable and passing this as a parameter to your select command:

var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("Value", typeof(string)));

for (int i = 0; i < hostnames.Length; i++)
{
    var dr = dataTable.NewRow();
    dr[0] = "";
    dataTable.Rows.Add(dr);
}
using (var connection = new SqlConnection("connectionString"))
using (var command = new SqlCommand("SELECT * FROM Table WHERE HostName IN (SELECT Value FROM @StringList)", connection))
{
    SqlParameter stringListParameter = new SqlParameter("@StringList", SqlDbType.Structured);
    stringListParameter.Value = dataTable;
    stringListParameter.TypeName = "dbo.StringList";
    command.Parameters.Add(stringListParameter);

    // OPEN CONNECTION EXECUTE COMMAND ETC

}

Osa E · Accepted Answer · 2013-02-22 19:06:10Z

1

There are 4 ways to achieve what you want. You choose what works best for you.

Use IN as in your code
Break down to OR. A IN (1, 2, 3) => A=1 OR A=2 OR A=3
Use Table Valued Parameters
User the sql query passed earlier. ex: "SELECT * FROM table WHERE hostname IN (Select hostname from tableusedEarlier)"

answered Feb 22, 2013 at 19:06

Osa E

1,7911 gold badge15 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to query from SQL comparing with array members?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related