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?
xml var?
I have just looked this posts:
How do I pass a string parameter greater than varchar(8000) in SQL Server 2000?
Check if concatenating to a varchar(max) will go beyond max allowable characters
And others through the web.
Regards.
Seriously - VARCHAR(MAX)
can store up to 2 GB of data - not just 8000 characters.....
Try this:
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....