A limit that I ran into was using stored procedures where you pass a variable.
If you need to pass a variable or use a declared variable in the procedure you will be limitted by the 8000 character variable limit.
I have had times when I was passing a "WHERE" clause as a variable and reached the 8000 character limit. I haven't found another way to do it within a stored procedure yet, so I have been passing the SQL statement directly to the database. This was an ASP application pulling historical records. I had to use OR rather than Between becuase of some other criteria.
Otherwise the SQL statement passed directly to the database has no hard limit as far as I know.