Manipulate a VARCHAR variable exceeding 8,000 characters

Question

I have a stored procedure in a SQL Server 2005 database that creates the body of an email message. The result of this procedure should be placed in a table with the TEXT field for the message body for further processing.

The problem I am facing is that in some scenarios the generated email is more than 8000 characters. Therefore, I cannot create a message body in a TEXT variable, because SQL Server does not allow TEXT type variables. I can copy the parts of the email into a table variable, but it does not solve the problem, because I can not add these parts of the email together in one variable to paste into the result table.

Is there a way (1) to manipulate objects larger than 8000 in memory, or is there a (2) way for me to accumulate values ​​in a field of a (temporary) table in a text field?

Answer 1

You can accomplish this using the NVARCHAR (max) or VARCHAR (max) data types in SQL 2005.

Big data types are similar to their smaller counterparts, varchar, nvarchar, and varbinary. This similarity allows SQL Server to store and retrieve large characters, Unicode, and binary data more efficiently.

SQL Server , , SQL Server. , , 2 ^ 31 , Unicode-.

http://msdn.microsoft.com/en-us/library/ms178158.aspx