append to a text data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

append to a text data type

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2002-08-13 : 14:15:02

How can I append something onto the existing value of a text variable in a stored procedure?

ie

SET @text = @text + @aLittleBitMore

I was looking at a funky proc to break the original into many 8000 varchars and then concatenating them all together, but what a pain!

Is there an easier way? I can't limit it to 8000 characters.

Thanks!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 14:27:20

Can't you use UPDATETEXT with an insert_offset of the datalength of the existing column?

Jay White
{0}

chedderslam
Posting Yak Master

223 Posts

Posted - 2002-08-13 : 14:37:20

I mean to a local variable in a stored procedure that will be passed to something else.

Sorry I wasn't very clear, but thanks for the suggestion.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 14:40:58

Couldn't you insert your @text into a table (temp or otherwise) and use UPDATETEXT against that, then repopulate your @text .... BLOBs are such a pain ...

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-13 : 17:02:54

You cannot modify a text variable. You can only pass it to a stored procedure, and you can only pass it as a literal value:

CREATE PROCEDURE spText @textvar text AS
SELECT @var
GO
--this won't work:
EXECUTE spText @anotherTextVar
--this does work:
EXECUTE spText 'This is a really long literal string and is the only way to use a text variable'

I know you don't believe me:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18744

But you CAN use temp tables to solve your WHERE clause problem. I'm willing to take a shot at it but I need more specific information. I can also tell you from LONNNNNNNNNNNG experience that you'll pull your hair out trying to get this kind of dynamic SQL to work:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10245

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources