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.

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.