Dynamic SQL Statement is too long

Question

I am using quite a long dynamic SQL Statement (a bit more than 13000 characters) but when I am trying to execute it, I am noticing that the exec isn't reading the statement completly and cuts the last part off.

I am using:

DECLARE @Statement nvarchar(max)
SET @Statement = N'[LONG STATEMENT]'
EXEC (@Statement)

I did notice, that it could read even less characters, if I am not using the the brackets in EXEC (@Statement)

I also tried using EXEC sp_executesql @Statement It just stops reading the statement after 12482 characters... I have the problems with SQL-Server 2008 R2 and SQL Server 2014

EDIT: OK, now I noticed something different. It seems, that the lenght itself of the statement is not exactly the problem. As I mentioned in a comment below, I am using this long dynamic sql statement, because I am creating an update script, which adds a new stored procedure and within this procedure I am using table names, which can differ. So I created variables, which contain the table names and used these variables with the dynamic sql statement, so I don't need to change the table names within the procedures and functions I am adding with this update script, but just changing the content of the variables. However, if I am NOT using these variables and use the table names "hardcoded" in the statement, the statement can be executed successfully...

Answer 1

I guess the answer is here:

So I created variables, which contain the table names and used these variables with the dynamic sql statement, so I don't need to change the table names within the procedures and functions I am adding with this update script, but just changing the content of the variables.

I guess, your dynamic T-SQL statement is built using string concatenation. So, let's say we have something like this:

DECLARE @DynamicSQLSTatement NVARCHAR(MAX);

DECLARE @TableName01 NVARCHAR(128) = 'T01';
DECLARE @TableName02 NVARCHAR(128) = 'T02';

DECLARE @TSQL NVARCHAR(4000) = REPLICATE(N'X', 4000);

SET @DynamicSQLSTatement =  @TableName01 + @TSQL + @TableName02;

We have three short strings (length < max) and when they are concatenated, we expect that the new NVARCHAR(MAX) value will be capable of storing the whole new string (it is with max length, after all).

So, the following statement will give as T02, right?

SELECT RIGHT(@DynamicSQLSTatement, 3);

But no, the output is XXX. So, the question is why the whole concatenation text is not preserved?

When you are concatenating nvarchar(1-4000) strings they output string is not converted to max if it is not possible to store all the data.

In order to fix this, we can cast the first part of the string to nvarchar(max):

SET @DynamicSQLSTatement =  CAST(@TableName01 AS NVARCHAR(MAX)) + @TSQL + @TableName02

SELECT RIGHT(@DynamicSQLSTatement, 3);