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
select @sql
to check if the string is really correct. – Magnuson--long string
, such as combining multiple short string literals using+
. Avarchar(max)
variable can actually hold even more than 2GB, assuming SQL Server 2008 or later. – GarlanSelect @sql
it is returning only the part of string – Enow+
for string concatenation inside my dynamic sql. I have usedCONCAT
function – EnowCONCAT
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. – Garlan@sql
variable and it is due toCONCAT
function. Fixed it by addingset @sql = CONCAT(@sql,'select..')'
– Enowconcat
function. Updated the question. I have mentioned it in my previous comment so he clarified it – Enow