Prdp Prdp - 1 month ago 9
SQL Question

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

Answer

It is probably this you are running against:

DECLARE @final VARCHAR(MAX);
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));
Comments