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?

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.

Answers


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


Need Your Help

sum cells of certain columns for each row

r rows

I would like to calculate sums for certain columns and then apply this summation for every row. Unfortunately, I can only get to the first step. How do I now make it happen for each row? I know tha...

How do I fix MSB3073 error in my post-build event?

visual-studio-2012 msbuild post-build-event

I'm working on a project that requires that DLLs generated by building my solution to be copied from the bin folder to another folder, both of which are on my machine, in my C drive. I've written a...