Varchar(Max) is not working in Exec

Question

I have a variable which has SQL string stored in it and am executing it through exec()

Declare @sql varchar(max)

set @sql = Concat('select...',@var,'..') -- large string 

exec (@sql)

but am getting error saying

Incorrect syntax near sometext

It is because the variable @sql cannot hold the entire string. So I fixed by splitting the string into two different variables and executed it

Declare @sql1 varchar(max),@sql2 varchar(max)

set @sql1 = 'select...' 
set @sql2 = ' from sometable join....'

exec (@sql1+@sql2)

I checked the data length of @sql1+ @sql2

Select Datalength(@sql1+ @sql2)

It returned 14677

Now question is why varchar(max) cannot store 14677 bytes of information? When the documents says it can store upto 2GB of data

Answer 1

It is probably this you are running against:

DECLARE @part1 VARCHAR(5000)=REPLICATE('a',5000);
DECLARE @part2 VARCHAR(5000)=REPLICATE('a',5000);

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(@part1+@part2);

The result is 5000,5000,8000

If one of the summands is a MAX type, you'll get the expected result

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CAST(@part1 AS VARCHAR(MAX))+@part2);

The result is 5000,5000,10000

This is often seen in connection with

• string concatenation
• usage of (older) functions returning VARCHAR(8000) as former max length
• column definitions

UPDATE Same with CONCAT

SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CONCAT(@part1,@part2));
SELECT DATALENGTH(@part1),DATALENGTH(@part2),DATALENGTH(CONCAT(CAST(@part1 AS VARCHAR(MAX)),@part2));