Question: I have an SQL Azure database with a table that has `JobId` primary key. A user sends in a bunch of "job ids" as an array that can contain one or more (maybe ten, maybe ten thousand) values. I can't decide how to do write SQL queries for that. I will use .NET framework classes.

One option I see is creating a temp table and doing a `WHERE IN` nested `SELECT` (SQL+C#-like pseudocode):

```
CREATE TEMP TABLE JobIdsTable
foreach( JobId in JobIdsFromClients ) {
   INSERT INTO JobIdsTable VALUES (JobId)
}
SELECT * FROM JobsTable WHERE JobId IN SELECT * FROM JobIdsTable

```

Here it looks cool from code writing standpoint, but I'm not sure it will always run fast.

Another option is creating a huge `OR` concatenation forming a giant `SELECT`:

```
SELECT * FROM JobsTable WHERE JobId='JobId1' OR JobId='JobId2' OR JobId='JobId3' .....

```

Here it's one `SELECT` but the idea of concatenating this longs string doesn't sound good (normally I'd use [`System.Data.SqlClient.SqlCommand.Parameters`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx) to build a parameterized query, but here it will be very complicated. Also I'm unsure of whether there's a limit on how long an SQL query can be.

I'd like my query to be both manageable and performant. So I see drawbacks in both options.

Which do I choose? Do I do it any other way instead?

Answer: 
Where do the jobIds come from, I can't imagine the IDs are coming from a form where a user types in 10,000 integers. 

Ideally what you want is some kind of "JobBatch" table that groups the IDs together, either through a JobBatchId FK on the job table or an intermediate JobBatchId table that realtes job batches to jobs if there is a many to many. Then whatever is generating the IDs just has to generate a "batch" and you only have to pass in a single id. 

There are workarounds for this by passing in a list of comma seperated is as a single parameter then splitting them out in the SP but with as many as 10,000 this is getting on for being a bad design. [Here](http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648) is a collection of split functions in case you wish to go down this route.
you can concatenate a long string of IDs (instead of ORs) and use a WHERE IN clausule like:

```
SELECT * FROM JobsTable WHERE JobId IN (12343,454353,24354,20200/*,...so on...*/)

```

This is quick to build and lightweight to manage by the sql server 

The IN syntax can accept only one value if you want as `IN (3939)` and if you want a dirty solution to do not check cero cardinality you can begin concatenating with `(0` and add as many `','+IDvalue` as you need and a final `)` (provided that 0 is not a valid JobId)