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

sql server 2014stringt-sql

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.

Best Answer

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.

Related Question