6

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

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • 1
    That is odd and unlikely. Did you `select @sql` to check if the string is really correct. – juergen d Nov 02 '16 at 10:05
  • 2
    You're probably hiding some sins where you've just written `--long string`, such as combining multiple short string literals using `+`. A `varchar(max)` *variable* can actually hold even *more* than 2GB, assuming SQL Server 2008 or later. – Damien_The_Unbeliever Nov 02 '16 at 10:06
  • @juergend - yes, When I do `Select @sql` it is returning only the part of string – Pரதீப் Nov 02 '16 at 10:07
  • 1
    This is the [sin I'm referring to](https://msdn.microsoft.com/en-us/library/ms177561.aspx): "If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur." - if you just have two string literals, neither will be a large value type in itself, and the variable you'll *eventually* store the result in is not part of the equation. – Damien_The_Unbeliever Nov 02 '16 at 10:08
  • Possible duplicate of [SQL NVARCHAR and VARCHAR Limits](http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits) – Yahfoufi Nov 02 '16 at 10:12
  • @Damien_The_Unbeliever - But I did not use `+` for string concatenation inside my dynamic sql. I have used `CONCAT` function – Pரதீப் Nov 02 '16 at 10:13
  • 2
    Well, the documentation for [`CONCAT`](https://msdn.microsoft.com/en-GB/library/hh231515.aspx) is hardly mysterious about this either - "If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type". It would be much easier for us to point to these type of errors, though, if we could *see* the code which contains the error, rather than being forced to guess about it. – Damien_The_Unbeliever Nov 02 '16 at 10:16
  • @Damien_The_Unbeliever - Got it.. That is the problem. Thanks – Pரதீப் Nov 02 '16 at 10:24
  • @Damien_The_Unbeliever - Code is so big that's why didn't post it. Now I understood the reason. It is not because of `@sql` variable and it is due to `CONCAT` function. Fixed it by adding `set @sql = CONCAT(@sql,'select..')'` – Pரதீப் Nov 02 '16 at 10:26
  • but where you have used concat function in your question? @Prdp – User Nov 02 '16 at 10:28
  • @Damien_The_Unbeliever, Here user is not using Concat function know – User Nov 02 '16 at 10:30
  • @Srinath - Am using `concat` function. Updated the question. I have mentioned it in my previous comment so he clarified it – Pரதீப் Nov 02 '16 at 10:31

2 Answers2

6

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));
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

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.

J Chur
  • 1