Dealing wih very long varchar/nvarchar variables?

Question

We have a script that creates the email body in html format, so that it can be sent with sp_send_dbmail.

So I have three variables: @tableHTML1 varchar(8000), @tableHTML2 varchar(8000), @tableHtmlComplete varchar(max).

After the tsql script is executed, the len of @tableHTML1 is 4768, @tableHTML2 is 4594. Since the @body parameter of sp_send_dbmail can only receive one variable, I ended up doing set @tableHtmlComplete = @tableHTML1 + @tableHTML2. Unfortunately, the len of @tableHtmlComplete is 8000, since that's the max size of a varchar variable.

So what can I do in this case? I need to email the contents of @tableHTML1 plus @tableHTML2.

I will only consider another solution if what I'm trying to do above is not feasible.

Thanks.

8000 bytes isnt the max size. VARCHAR(max) can hold 2gb of data. — dfundako, Dec 27 '16 at 20:10
Bytes not bites. And http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits/12639972#12639972 — Martin Smith, Dec 27 '16 at 20:13
I ended up just declaring all variables as `varchar(max)` and the email was sent correctly. — fdkgfosfskjdlsjdlkfsf, Dec 27 '16 at 20:21
I was just under the impression that concatenating two `varchar(max)` into one `varchar(max)` would hit some limit. I was obviously wrong. — fdkgfosfskjdlsjdlkfsf, Dec 27 '16 at 20:23

score 4 · Accepted Answer · answered Dec 27 '16 at 20:10

Convert them to varchar(max) before concatenating them:

DECLARE @tableHTML1 varchar(8000), @tableHTML2 varchar(8000), @tableHtmlComplete varchar(max)

SET @tableHTML1 = REPLICATE('a',4768);
SET @tableHTML2 = REPLICATE('a',4594);
SET @tableHtmlComplete = CONVERT(VARCHAR(MAX),@tableHTML1) + CONVERT(VARCHAR(MAX),@tableHTML2);

SELECT DATALENGTH(@tableHtmlComplete);

Result: 9362

score 0 · Answer 2 · answered Dec 27 '16 at 23:16

0

As a side note: you can simplify Lamak's solution by converting just the first value to varchar(max) like so:

SET @tableHtmlComplete = CONVERT(VARCHAR(MAX),@tableHTML1) + @tableHTML2;

answered Dec 27 '16 at 23:16

Alan Burstein

7,770
1
15
18

Dealing wih very long varchar/nvarchar variables?

2 Answers2