how to create text variable

Question

how to create text variable

zishanz

SSCommitted

Points: 1852
More actions
April 11, 2002 at 8:45 pm

#77971

Hi, I need to create a variable to hold very
long string ( more than 8000chars).
I try to create a text type variable, as @mv_textvariable, or @@mv_textvariable,
but the server complaints that can't declare a local variable of 'text' type.
I don't care if it's local or global, as long as the variable can hold a very long string.
I can't break the string into multiple strings. Actually, I am concating multiple strings into a very very long string then insert in into a column of text type.
Any idea or workaround? Thank you very much.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Sorry the only work around is to split into multiple varibles and concatinate into the text field. unfortunately the largest available datatype that a variable can exist as is CHAR or VARCHAR max 8000 chacters. If somebody has a way to do in one I would be interested but doubtfull will find.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

What if you declare an input parameter as text, wouldnt it be accessible within the proc? Why don't you build the string on the client and actually send over as a text col?

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Eve SSCrazy Points: 2480 More actions · Answer 3

"I am concating multiple strings into a very very long string then insert in into a column of text type. "

Why can't you just add your multiple strings to the value of the column like:

update Mytable

set TXTCol = TXTCol + @str1 + @str2 + @str3 etc

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Sorry forgot about the fact text can be a varibale type for an input parameter. Any other way will not work.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

zishanz SSCommitted Points: 1852 More actions · Answer 5

Thank you all for your help.

If I use an input text variable, I got this error:

create procedure my_text (@input_1 varchar,

@input_2 text )

AS

BEGIN

declare ...

set @input_2 = @mv_var1 + @mv_var2 + ..

END

GO

then, I got this error:

The assignment operator operation cannot take a text data type as an argument.

I am thinking about set TxtCol = TXTCol + @str1 + @str2 .., but haven't figured out a good logic loop.

My issue is this:

I can't build the string at client side because all data is at the server side.

my table is like this:

mv_table

---------------------------------------

uid(numeric),ques_id(int),answer(varchar(4000)

for each user, it might have couple of rows in the table, the data might be like this:

00001, 1, 'answer to question 1'

00001, 2, 'answer to question 2'

00001, 3, 'answer to question 3'

00002, 1, 'uid2 answer to question1'

00002, 4, 'uid2 answer to question4'

00003, 2, 'uid3 answer to question2'

.....

My task is to concat all answers by uid as 1 record and export them.

so the data will be like this:

00001,'answer to question1,answer to question2...'

00002, 'answer to question1, answer to question4...'

Then insert those rows to a temp table for exporting by using bcp.

What I am doing right now for concating answer column is like this:

select all rows to a temp table variable

as (id int identiy, user_id, answer)

Then loop through the temp table variable using while loop, concating answer together for the same user_id. That's WHY I need a text type variable to hold the concating result.

After concat, I just insert the concating result into my temp table ( which only has one column: content (text)

If I do what epol29 has suggested, then I think I need to change my temp table as two columns, user_id (or id ), content (text)

But I still need some help for assigning the

ColTXT the concat result ( from the while loop?)

Any kind of suggestion, workaround is appreciated. The main purpose of this task is to get user's answers to all questions as one

string ( in csv format), so when the file is returned to the client side, all the data will be displayed in an excel sheet, one user per row in the excel sheet.

Andy Warren SSC Guru Points: 119902 More actions · Answer 6

How about using XML instead? You can get it in a single select, no cursor. Should be able to load it directly, or you could apply a transform to convert it to ascii (or whatever).

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

zishanz SSCommitted Points: 1852 More actions · Answer 7

Hi, Andy.

Thanks for the reply. Unfortunately, we are not using XML or change any code for the frontend.

The project is a pure translation of oracle packages to sql server stored procedures.

Thanks anyway.