I was using a dynamic SQL query and I come across an error. Find below the code:
DECLARE @tablename NVARCHAR(128); -- Setting tablename dynamic and its length 128 characters
DECLARE @sql NVARCHAR (MAX); -- Setting dynamic sql query length maximum
SET @tablename = 'table_name_123' + FORMAT(DATEADD(DAY, -2, GETDATE()), 'MMMdd_yyyy')
SET @sql = '
--------- 150 line code ----------
';
print(@sql)
EXEC sp_executesql @sql;
Problem: My query is only running for 128 lines not for 150 lines.
Note: I am using SSMS
Since, I have already declared length of SQL as MAX, why it is taking automatic length of 128 which was previously specified for tablename. When I changed length of tablename from 128 to MAX, whole code was working well.
Parl Harbour is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
12
The problem, as siggemannen mentions, is still likely truncation.
You have declared your variable, @sql
, as an nvarchar(MAX)
, however, the values you are concatenating to then be assigned to your variable are not of MAX
length. When you concatenate values, they are not implicitly concatenated to MAX
length if the number of characters exceeds 8,000 bytes. For example. SELECT REPLICATE('A',7999) + 'AA';
would only return 8000 A
characters, not 8001, as the data type of the string would be a varchar(8000)
, and so the final A
is truncated.
The same is likely happening here; you are concatenating many non-MAX
string and expecting a MAX
length value. That your variable is a MAX
isn’t relevant here, as the assignment (and conversion) occurs after the expression is defined. If it didn’t, then something like DECLARE @Date date = '2022' + '10' + '12';
would generate an error, as none of those string are valid dates on their own.
I find the easiest way to solve this problem is to start off my concatenating an empty MAX
length value (of the correct type) to the string at the start. This means that every value concatenated afterwards is then also implicitly converted to a MAX
value:
SET @sql = CONVERT(nvarchar(MAX), N'') +
N'
--------- 150 line code ----------
';