I tested with the code below and varchar(max) acts as expected when concatenating with the + operator when the number of rows are known.. However a programmatic solution still seems elusive...

DECLARE @C VARCHAR(MAX)

DECLARE @Code VARCHAR(20)

DECLARE @Value VARCHAR(MAX)

DECLARE @Temp TABLE(

RowID INT IDENTITY(1, 1),

Code VARCHAR(20),

MaxValue VARCHAR(MAX))

INSERT INTO @Temp (

Code,

MaxValue

) VALUES (

/* Code - VARCHAR(20) */ 'A note',

/* Value - VARCHAR(MAX) */ REPLICATE(CAST('A' AS VARCHAR(MAX)), 10000 ) )

INSERT INTO @Temp (

Code,

MaxValue

) VALUES (

/* Code - VARCHAR(20) */ 'B note',

/* Value - VARCHAR(MAX) */ REPLICATE(CAST('B' AS VARCHAR(MAX)), 10000 ) )

SET @C = ''

SELECT @Code = Code, @Value=MaxValue FROM @Temp WHERE RowID = 1

SELECT @C = @Code + @Value

SELECT @Code = Code, @Value=MaxValue FROM @Temp WHERE RowID = 2

SELECT @C = @C + @Code + @Value

SELECT @C

SELECT LEN(@C)

len output: 20012