Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.
Other than that, your query is limited by runtime conditions. It will usually run out of stack size because x IN (a,b,c) is nothing but x=a OR x=b OR x=c which creates an expression tree similar to x=a OR (x=b OR (x=c)), so it gets very deep with a large number of OR. SQL 7 would hit a SO at about 10k values in the IN, but nowdays stacks are much deeper (because of x64), so it can go pretty deep.
Update
You already found Erland's article on the topic of passing lists/arrays to SQL Server. With SQL 2008 you also have Table Valued Parameters which allow you to pass an entire DataTable as a single table type parameter and join on it.
XML and XPath is another viable solution:
SELECT ...
FROM Table
JOIN (
SELECT x.value(N'.',N'uniqueidentifier') as guid
FROM @values.nodes(N'/guids/guid') t(x)) as guids
ON Table.guid = guids.guid;