How to store a string var greater than varchar(max)?

Question

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.

Answer 1

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....