Best Method for Handling Long Strings in Stored Procedures?

What is the best way to handle long strings in stored procedures, especially strings that my exceed 8000 characters?

I have a procedure that retrieves a couple groups of sales records that require follow-up for each salesman. This information is then formatted into the body of an HTML email (the number of data items for each quote require the use of tables to be readable in the email) by appending the necessary HTML and record data to a VARCHAR(8000) variable which is ultimately used as the body of the email.

The procedure functions great but I have encountered two unexpected issues I need some help resolving.

1. How can I handle strings longer than 8000 characters? It looks like if I have more than about 12 – 15 records returned by the time I add the HTML I am exceeding this limit and the balance is just truncated.

2. There seem to be "breaks" that are inserted in string/variable based on criteria I haven't figured out. Occasionally these occur in the middle of one of the HTML tags and break the corresponding rendering. I have tried, with limited to success to prevent this by inserting CHAR(10) characters at appropriate places to keep line lengths to about 80 characters but that consumes valuable character space and seems to only be about 60% effective. Can anyone enlighten me on what is causing this?

Thanks for your help.For item 1 use the ntext field type. You can store an unlimited length text value in an ntext field. You can set and get data with an ntext field just like you would with a varchar. You can't use ntext fields quite like you would a character fields (e.g., you can't use it in a WHERE clause) but if you just want to save and retrieve it you should be ok.

For item 2 I don't know, I haven't encountered that problem.|||I am using a variable to hold the string in the stored proc as it is assembled. It was my understanding that text and ntext fields could not be used for variables. Are you creating a cursor with an ntext field or what?

What I am trying to do it insert some text into the variable (header) then append some more text (section header) then I iterate throught the results from the select statement appending each returned record to the variable. Then I append more text (2nd section header) and again iterate throught those select results and append each. Then I return this now very fat variable to the calling proc which uses it as the body of the email.

Is there a better approach for this type of task? Basicaally I am trying to dynamically build an email body that can exceed 8000 characters with some regularity.

Thanks,

--Marshall|||It might be easier to do the string building from a calling program (C#, VB.Net). I don't know of a way to concatenate ntext fields in a sproc. When I have used ntext fields they just pass into and out of a sproc. The sproc itself doesn't manipulate them.