CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sqlvarcharmax
Pரதீப்
Pரதீப்

Reputation: 93734

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

Upvotes: 6

Views: 3292

Answers (2)

J Chur
J Chur

Reputation: 1

The approach of creating two varchar(max) data elements and combining them via "exec (@sql1+@sql2)" works and I appreciate the suggestion. I ran into the same issue and will be using this trick in the future.

For me, one varchar(max) data element got truncated when attempted to be executed. Split it into two varchar(max) data elements (no syntax change) and executed without issue.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 6

Related Questions