IN List (How many maximum?)

Question

Post reply

IN List (How many maximum?)

RandyB

SSCommitted

Points: 1565
More actions
October 26, 2005 at 8:54 am

#90687

We have a query that is blowing off.
What I need to know is how many items are allowed in an IN list?
Example
SELECT V1, V2, V3 FROM Table1 WHERE V2 IN (?,?,?,?..........)
I am not finding anything in BOL.
Thanks.
Randy

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

In is unlimited AFAIK. But usually a join is a better way to go as rsharma already stated.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 2

Why do you need many items in the IN (, , , ) list ? Can't you write a filter condition or a sub-query (or a co-related sub-query) with filters that will qualify for all those values that you want to put into the IN clause ?

Example:

SELECT V1, V2, V3 FROM Table1 WHERE V2 > ? AND V2 < ?

or

SELECT V1, V2, V3 FROM Table1 WHERE V2 IN (SELECT X FROM T WHERE....)

or

SELECT V1, V2, V3 FROM Table1 WHERE EXISTS (SELECT 1 FROM T WHERE COL1 = TABLE1.V2 AND COL2 = ?)

Basically some SQL way of identifying those IN list data values.

RandyB SSCommitted Points: 1565 More actions · Answer 3

Reason for this method is, the query is from a COGNOS report prompt. When a user selects a contractor, a distinct list of recipients is generated and the only option for the user is to select individuals or hit a 'select all' button.

I have the developer looking at adding an '-All-' choice at the top of the dropdown list to leave that filter off.

However, I was hoping to see a definitive maximum number that can be used in the IN () list.

Thank you.

Randy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I don't think there should be any limit on the in(), but I'd definitly go with the all option leaving that out of the query if possible .

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 5

I don't think there is a limit on the IN clause, but might you be hitting the 8000 char limit on SQL statement?

Mark

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Good point.... I'm hoping you're not using dynamic sql for this?

RandyB SSCommitted Points: 1565 More actions · Answer 7

Yes, it is dynamic sql generated by Cognos. I havent been able to get the sql statement so I can run it in QA and optimize it.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 8

Perhaps you may want to run Profiler and capture the SQL it is generating to see. You should be able to filter in Profiler by something (workstation? application? database? to get only the queries you want.

Hope this helps.

Mark

Martin Schoombee SSCoach Points: 19128 More actions · Answer 9

I've had this issue before, runnning a query from an Oracle db through DTS. I cannot exactly remember the amount, but it was something in the region of 2000 or 3000. This might however be an Oracle limit, and not SQL Server related at all.

Martin.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 10

This is one of those questions where, 'if you need toknow the answer, you're probably doing something wrong'!

I would suggest writing the list to a temporary table and doing a join on that - but the 8k limit also is a good point. With the temporary table option, you could keep passing values in chunks and accumulating in the table, then running the query. This would be unlimited for all practical purposes.

RandyB SSCommitted Points: 1565 More actions · Answer 11

Thanks everybody for your ideas. I had thought the problem was the number of vars in the IN list but probably the total size of the SQL statement was the problem.

I kicked the problem back to the developer for him to change his method of selecting individuals, instead of selecting all individuals individually, to just omit that particular filter or use something like WHERE billnum in ('%') if that filter needs to be included by Cognos.

Thanks again.

Randy