How to Get a SQL String Variable to be Larger Than 4000 Characters or 8000 Characters for Dynamic SQL

Sometimes desperate messages are required because SQL Server parameterisation does not work (i.e. can’t use a variable in place of a table name). Recently I was constructing some dynamic SQL and the code was failing execution because the SQL dynamic length was greater than 4000 characters or 8000 characters. The latter was failing; even though the defined datatype was NVARCHAR(MAX) !!!

To resolve the issue I had to force SQL Server database engine to maintain the NVARCHAR(MAX) datatype. SQL below shows how it was done:

Example SQL of Issue

DECLARE @SQL NVARCHAR(MAX);
SET @SQL =  N'PRINT ''David Selwood'''
				+ REPLICATE(N'A',4000)
				+ REPLICATE(N'B',4000)
				+ REPLICATE(N'C',4000)
				+ REPLICATE(N'D',4000)
				+ REPLICATE(N'E',4000)
				+ REPLICATE(N'F',4000)
				+ REPLICATE(N'G',4000);
SELECT LEN(@SQL);
--EXECUTE  (@SQL);
--EXECUTE  [sys].[sp_executesql] @stmt = @SQL;
GO

When the latter executes, a length of 4000 is returned, which is of course incorrect.

Example SQL of Issue Fixed

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = CAST('' AS VARCHAR(MAX)); /*Required because SQL is over 4000 or 8000 characters.*/
SET @SQL = @SQL 
				+ N'PRINT ''David Selwood'''
				+ REPLICATE(N'A',4000)
				+ REPLICATE(N'B',4000)
				+ REPLICATE(N'C',4000)
				+ REPLICATE(N'D',4000)
				+ REPLICATE(N'E',4000)
				+ REPLICATE(N'F',4000)
				+ REPLICATE(N'G',4000);
SELECT LEN(@SQL);
--EXECUTE  (@SQL);
--EXECUTE  [sys].[sp_executesql] @stmt = @SQL;
GO

When the latter executes, a length of 28021 is returned, which is of course correct.

A Second Example of Issue Fixed

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = REPLICATE(CAST('SELECT ''David Selwood''AS[Name];'AS NVARCHAR(MAX)),9999);
SELECT LEN(@SQL);
--EXECUTE  (@SQL);
--EXECUTE  [sys].[sp_executesql] @stmt = @SQL;
GO

When the latter executes, a length of 309969 is returned, which is of course correct.

This entry was posted in SQL 2008 R2, SQL 2016, Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 4 + 10 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)