Manipulate VARCHAR variable larger than 8000 characters

Question

I have a stored procedure on an SQL Server 2005 database that is creating the body of an e-mail. The result of this procedure must be placed in a table that has a TEXT field for the message body for later processing.

The problem I've encountered is that in some scenarios the e-mail to be generated is larger than 8000 characters. So I can't build the message body in TEXT variable since SQL Server doesn't allow variables of type TEXT. I can accumulate the parts of the e-mail into a table variable but doing so deoesn't solve the problem because I cannot append these e-mail parts together into a single variable for inserting into the results table.

Is there a way to (1) manipulate entities larger than 8000 in memory or is there (2) a way for my to accumulatethe values in the field of a (temporary) table into a text field?

Answer 1

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

The large-value data types are similar in behavior to their smaller counterparts, varchar, nvarchar and varbinary. This similarity enables SQL Server to store and retrieve large character, Unicode, and binary data more efficiently.

With large-value data types you can work with SQL Server in a way that was not possible using the text, ntext and image data types from earlier versions of SQL Server. You can define variables that can store large amounts of data, up to 2^31 bytes of character, binary, and Unicode data.

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