sqlsql-servert-sqldynamic-sqlvarcharmax

Varchar(Max) is not working in Exec

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

Solution

  • 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

    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));