How to write a SQL request that selects rows with a column value equal to one of multiple values?

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 strin开发者_如何学运维g doesn't sound good (normally I'd use System.Data.SqlClient.SqlCommand.Parameters 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?

I'd go with the temp table, but use a join instead of a subquery. if the Id columns are indexed, the performance should be decent:

SELECT * 
FROM JobsTable j
INNER JOIN JobIdsTable jt ON j.JobId = jt.JobId

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 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)