Max Length for Varchar Datatype Variable in T-SQL

There is a limit on how long a varchar datatype variable can be. It is 8,000. It obviously not enough since this kind of variable generally used dynamical SQL, especially when use with loop statement to add up with the query statement.

The solution? Don't concatenate it. Instead, execute it often and save into a WIP temporary tables. Sure, you would need to programmatically delete these temporary tables at the end of your procedure.

http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx