T-SQL VARCHAR(MAX) Truncated

Question

DECLARE @str VARCHAR (MAX);

SELECT @str = COALESCE(@str + CHAR(10), '') +
       'EXECUTE CreateDeno ' + CAST(ID AS VARCHAR) 
FROM   GL_To_Batch_Details
WHERE  TYPE = 'C' AND
       Deno_ID IS NULL;

--PRINT @str;--SELECT @str;
**EXEC(@str);**

EDITED

Does EXECUTE statement truncate strings to 8,000 chars like PRINT? How can I execute a dynamic SQL statement having more than 8,000 chars?

Any suggestion would be warmly appreciated.

Answer 1

PRINT is limited to 8k in output.

There is also an 8k limit in SSMS results pane.

Go to

tools -> options -> query results

to see the options.

To verify the length of the actual data, check:

SELECT LEN(@str)

Answer 2

When concatenating strings and the result is of type VARCHAR(MAX) and is over 8000 characters, at least one parameter and/or element being used in the concatenation need to be of the VARCHAR(MAX) type otherwise truncation will occur in the resultant string and will not be executable in an EXEC statement.

Example:

DECLARE @sql AS VARCHAR(MAX);
/* DECLARE @someItem AS VARCHAR(100); -- WILL CAUSE TRUNCATION WHEN @sql HAS LEN > 8000 */
DECLARE @someItem AS VARCHAR(MAX); -- All string variables need to be VARCHAR(MAX) when concatenating to another VARCHAR(MAX)

SET @someItem = 'Just assume the resulting @sql variable goes over 8000 characters...';
SET @sql = 'SELECT Something FROM Somewhere WHERE SomeField = ''' + @someItem + '''';

EXEC (@sql);
--PRINT @sql;

More information on MSDN.

"If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur."