What is the maximum number of parameters I can pass using the SQL IN clause in SQL server 2008 and SQL server 2000?

There isn't a quantity limit on the number of items in the IN clause. The only limit is that the entire batch cannot be more than the batch size maximum. In SQL 2008, this is 65,536 * Network Packet Size. In SQL 2000, I'm not sure if the limit was smaller than that.

This link describes these limits for 2008

You're asking the wrong question. You're asking "what is my tolerance for pain when doing x?" when you should be asking "how can I reduce my pain by NOT doing x?"

For the SQL Server 2008 side, instead of passing a huge list of values into a single parameter, and then needing dynamic SQL or string parsing to actually treat it like a proper IN, why not use table-valued parameters? Here is an example.

For SQL Server 2000, you will need to use the old methods. Some workarounds to reduce the overall load include XML and bit-packing.