Varchar (Max) does not work in Exec

Question

I have a variable that has an SQL string in it and I execute it through exec()

 Declare @sql varchar(max) set @sql = Concat('select...',@var,'..') -- large string exec (@sql) 

but i get an error

Incorrect syntax near sometext

This is because the @sql variable cannot contain the entire string. Therefore, I fixed the line break 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 @sql1+ @sql2

 Select Datalength(@sql1+ @sql2) 

He returned 14677

Now the question is why varchar(max) cannot store 14677 bytes of information? When documents say that it can store up to 2GB data

Answer 1

You are probably working against:

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

Result - 5000 500, 8000

If one of the terms is of type MAX , you will get the expected result

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

The result is 5,000,000, 10,000

This is common in connection with

• string concatenation
• use of (older) functions returning VARCHAR(8000) as the previous maximum 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));