varchar(MAX) text cuts off while going more than 8000 characters inside a procedure

Question

I have a procedure in which i have declared a variable with datatype varchar(MAX),

declare @str varchar(MAX);

set @str='select * from Table1...'

print (@str);
exec (@str);

but when the text written inside @str goes above 8000 characters (i.e. 8193 characters) it cuts off the string and prints only 8000 characters and executing it gives error off course.

I have tried 2 solutions after some search, but it doesn't work.

1) I have tried using 2 variables of same type "varchar(MAX)", and concat it at the time of execution, but doesn't works.

2) I have tried to cast "into varchar(MAX) again" both the variables before concatinating it, and then execute it, but that also doesn't works.

Answer 1

SSMS cannot display a varchar greater than 8000 characters by default.

However, it can store a varchar(max) value up to 2GB.

You could set up a loop and display "chunks" of the @str data, using an 8,000 character chunk size.

This answer will show you how confirm the actual length of the @str value in memory.

And this MSDN article discusses execute. In particular:

In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server 2005, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

This implies that the error you are receiving is something else, not a result of a truncate operation on your @str value.

Answer 2

The behavior you're seeing is not a problem with SSMS. It's SQL server trying to be storage-efficient by deciding that your varchar(max) is really a varchar(8000).

Credit where due, I found this article that explained the behavior I was seeing when I concatenated a string shorter than 8000 characters to another long string, which should have resulted in a combined string longer than 8000 characters. The resulting string was truncated to 8000 characters because the CAST is done before the concatenation.

You can reproduce this issue with this code:

DECLARE @s varchar(max) = REPLICATE('a',8000) + 'b'
SELECT @s, LEN(@s) -- this is 8000, but should be 8001

A workaround is to start your string with an empty nvarchar(max) before concatenating the next value:

DECLARE @s varchar(max) = CAST('' AS varchar(max)) + REPLICATE('a',8000) + 'b' --force varchar(max) 
SELECT @s, LEN(@s) -- this is 8001

Also, from Erik Darling's linked article: "if you're using varchar(max), you need to make sure to always cast all your strings to varchar(max) explicitly"

I've found that CASTing the first string is sufficient for SQL server to determine the whole string is varchar(max).