How to store a string var greater than varchar(max)?
I'm trying to do this:
DECLARE @myVar VARCHAR(MAX) Loop with cursor select @myVar = @myVar + bla bla bla end loop
When the loop ends, @myVar is incomplete, containing only 8000 characters.
I have tryed to use text, but is not allowed to local vars.
What would be a good solution to this case?
I have just looked this posts:
And others through the web.
Seriously - VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....
DECLARE @myVar VARCHAR(MAX) = '' DECLARE @ix INT = 1 WHILE @ix < 1000 BEGIN set @myVar = @myVar + CAST('bla bla bla' AS VARCHAR(MAX)) SET @ix = @ix + 1 END SELECT DATALENGTH(@myvar)
This will return a value higher than 8000 characters after 1000 iterations.
The point is: if you're using varchar(max), you need to make sure to always cast all your strings to varchar(max) explicitly - as I did in this example. Otherwise, SQL Server will fall back to "regular" varchar processing, and that's indeed limited to 8000 characters....