Just ran into a major headache when concatenating several @varchar(max)
variables together to build an email based on several different queries.
For efficiencies sake, I was using several varchars
to build the email at once, rather than going through roughly the same query two or three or more times to build it using only one varchar
.
This worked, right up until my varchars
got to longer than 8000 characters. Then the concatenation of them all into one varchar
(which I could shove into the @body
parameter of msdb.dbo.sp_send_dbmail
) returned "", and even LEN() wouldn't actually give me a length.
Anyhow, I've gotten around this by doing roughly the same queries several times and building the email with only one varchar(max)
.
TL;DR
I'm not happy with the solution. How could I have appended these varchar(max)
variables to each other?
One thing I've hit in the past which may or may not help here: SQL seems to "forget" what datatype its working with when you concatenate varchar(max). Instead of maintaining the MAX, it devolves to conventional varcharnitude, meaning truncation at 8000 characters or so. To get around this, we use the following trick:
Start with
SET @MyMaxVarchar = @aVarcharMaxValue + @SomeString + @SomeOtherString + @etc
and revise like so:
SET @MyMaxVarchar = cast(@aVarcharMaxValue as varchar(max)) + @SomeString + @SomeOtherString + @etc
Again, this may not help with your particular problem, but remembering it might save you major headaches down the road some day.