nvarchar(max) - 4000 limit overcome

Try:

DECLARE @sql as NVARCHAR(MAX);

SET sql = CONCAT (CAST('' AS NVARCHAR(MAX)), 'SELECT    ...    ');

EXEC sp_executesql @sql

=>

Microsoft documents this behavior with regard to the use of nvarchar(max) with sp_executesql. The official documentation notes that sp_executesql accepts a parameter of type nvarchar(max), which theoretically allows processing of strings up to 2^31-1 characters. However, there are practical limitations, especially when strings are passed directly as literals or concatenated.

A common problem arises when a long SQL string is passed directly as a literal to sp_executesql. In such cases, SQL Server treats the literal as nvarchar(4000) by default, which results in truncation of the string if it exceeds 4000 characters. This behavior is described in various community discussions and technical articles.

To work around this problem, it is recommended to store the SQL string in a variable of type nvarchar(max) and pass this variable to sp_executesql. This ensures that the entire string is processed without truncation. This practice is recommended in Microsoft documentation and various technical articles.

In summary, correct handling of nvarchar(max) in conjunction with sp_executesql is critical to avoiding string truncation issues. It is important to be aware of the default limits and take appropriate measures to ensure that long SQL statements are processed completely.