problem with IN clause in SQL

We have observed that there seems to be a maximum number of ids/variables which one can pass in the IN clause of开发者_StackOverflow SQL as comma separated values. To avoid this we are storing all the ids in a table and doing a SELECT within the IN clause. This however means extra database operations to store and retrieve ids. Is there any other way to use IN without SELECT?

Regards,

Sameer

In SQL Server 2008 you can declare a table variable and pass it to your query from the client or between the procedures.

For a modest number of values I would not have thought an IN (SELECT ..) would be that expensive on any rdbms.

You could INNER JOIN you IDs table or just break down the INs:

WHERE X IN (123,456 ...)
   OR X IN (789,987 ...)
   ...

Agree with Alex

Instead of

Select * From TableA Where ID IN (Select ID from IDTable)

Use

Select * From TableA
INNER JOIN IDTable ON TableA.ID = IDTable.ID

The join will automatically filter the IDs for you.

If you put it in atable why are you still using the in clause, why not just join to the table?