8

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.

5
  • 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/… Commented Aug 25, 2017 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
    Commented Aug 25, 2017 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
    Commented Aug 25, 2017 at 18:44
  • Give full code that reproduces the issue. Not some much shorter fragment which doesn't. Commented Aug 25, 2017 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
    Commented Aug 25, 2017 at 18:50

2 Answers 2

12

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.

2
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 @ErikDarling'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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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