Search code examples
sqlsql-server

Nvarchar(max) shrinks to 4000 chars in SQL Server

I am creating a dynamic query in stored procedure, and when I try to print that dynamic query, it only prints a part of the whole query.

My variable that holds the whole dynamic query is declared like below

DECLARE @SQL NVARCHAR(MAX)

When I print the variable's length like below, It gives the length of the whole query, which is good.

PRINT LEN(@SQL)

But, when I print the script itself, It prints only a part of the whole query.

PRINT @SQL

I also tried to print it like below

PRINT CONVERT(NVARCHAR(MAX),@SQL)

Why it only prints first 4000chars? What am I doing wrong?

Solution

  • You are not doing anything wrong. The Print command is limited to outputting 4000 characters (see BOL - Books Online, for more details). It does not mean nvarchar(max) has shrunk to 4000 characters.