Dynamic SQL character length exceeding 8000

With a dynamic SQL character length of more than 8000, I remember using sp_executesql in SQL SERVER 2008 to break this limitation.

Normally with dynamic SQL, EXEC () may be used, but there is a limit of 8000 string lengths. Since SQL SERVER 2005, it has been mentioned in INSIDE Server 2005 T-SQL programming that using sp_executesql () and nvarchar (MAX) can exceed the 8,000-character limit. Because sp_executesql () must use Nvarchar,nchar,ntext as a parameter, the maximum character limit for sp_executesql () is 4000.

Let's look at a nvarchar (4000) Example:

How to break this limit, let's look Again (set the SQL character variable type to nvarchar (MAX)):

As you can see, a dynamic SQL with a SQL character length of 9999 was successfully executed.

See an example of more than 100000 SQL characters:

String length is huge, I can only save the results to the RPT file, unfortunately, my machine (2.5 memory + 32-bit WIN2003) still can not display, must be insufficient resources

Dynamic SQL character length exceeding 8000