5

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.

  • 1
    What error message do you get? You should not have a problem executing it but printing it and having it truncate at 8000 characters is the expected behavior: stackoverflow.com/questions/7850477/… – Mr.Brownstone Aug 25 '17 at 18:34
  • It's my experience that it is storing the value, it just isn't displaying all of it in the query window when you select/print. In the query window try select substring(@str, 1,8000), substring(@str, 8001,8000), etc. Do you see the extended parts of the string then? I've done this in the past to see the entirety of xml that had been stored in a field – indiri Aug 25 '17 at 18:41
  • I had this same problem in the beggining of my carrer. What you need to do is to use some kind of cursor, or a better loop. – Racer SQL Aug 25 '17 at 18:44
  • Give full code that reproduces the issue. Not some much shorter fragment which doesn't. – Martin Smith Aug 25 '17 at 18:44
  • 1
    Use cast as XML on the varchar(max) column, this will return it as an XML string, which can be up to 2GB in size (click on the column to see the full value in a new SSMS tab). – Nic Aug 25 '17 at 18:50
9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.