I'm doing Select * From table Where Col IN (123,123,222,....)
If I put more than ~3000 numbers in the IN clause SQL throws and error.
Does anyone know if there's a size limit or anything?!!
Thank you.
views:
825answers:
4Depending on the database engine you are using, there can be limits on the length of an instruction.
SQL Server has a very large limit:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
ORACLE has a very easy to reach limit on the other side.
For so large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.
There is a limit, but you can split your values into separate blocks of in()
Select *
From table
Where Col IN (123,123,222,....)
or Col IN (456,878,888,....)
Why not do a where IN a sub-select...
Pre-query into a temp table or something...
create table SomeTempTable as select YourColumn from SomeTable Where UserPickedMultipleRecordsFromSomeListOrSomething
then...
select * from OtherTable where YourColumn in ( select YourColumn from SomeTempTable )
Depending on your version, use a table valued parameter in 2008, or some approach described here: