@Var into Two Parameters Using Temp Table

[jw2000]

Due to SQL limitation to varchar(8000) for local variables, I am trying to using a Temp Table to break the values into three varchar (8000). I would like to use another TEMP table in the code below, so @body can be TEXT and allocate the @body value into three varchar (8000) variable so I can use at ######. Any suggestions on how I can do this?


Current Code Is:

DECLARE @body VARCHAR(8000) --need to change this

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTable (...)
SELECT (...)

SELECT @body = ...
FROM #TempTable
ORDER BY ID

--add to @body
SET @body = (...)

Truncate table #TempTable
FETCH NEXT FROM ...

######

 

[jw2000]

Can anyone help?

 

[SQLBill]

What are you putting into a variable that has to be 24,000 characters long?

If you really have to do it, can't you make @body TEXT?

Knowing more of what you are trying to do will help get answers.

BTW-even though this is an international site, I've noticed that most posters do so during the 8 to 5 o'clock US Eastern Standard Time. Which explains why you haven't gotten any responses. Also, some times we have to ponder the question for a while to come up with our best response.

-SQLBill

Posting advice: FAQ481-4875

 

[jw2000]

Yes I am trying to build 3 varchar(8000) variables from a TEXT value in a temp table.

@body cannot be TEXT because SQL server does not allow the use of TEXT datatype for local variables.

 

[jw2000]

Creating a TEMP table with a TEXT column and / or 3 varchar(8000) columns would be a round about way to do this.

 

[jw2000]

Can anyone help?

 

[druer]

Check out READTEXT in Books on Line for how to read the values (using offsets) from a TEXT field. I think you are basically looking to say:

Field1 = TEXTfield bytes 1-8000
Field2 = TEXTfield bytes 8001-15999
Field3 = TEXTfield bytes 16000-23999

The online documentation for READTEXT should get you where you want to go.

 

[jw2000]

A better solution is to have three varchar (8000) parameters:

@body1 VARCHAR(8000)
@body2 VARCHAR(8000)
@body3 VARCHAR(8000)

But in the while loop after I create #TempTable, I need to somehow assign values for the first 8000 characters into @body1, the second 8000 characters into @body2, and the third 8000 characters into @body3. I don't know how many characters there are in total for:

INSERT INTO #TempTable (...)
SELECT (...)

I need to be able to use the values for @body1, @body2 and @body3 after the WHILE loop at ######

 

[druer]

My suggestion is to use your @body1 and use the READTEXT to populate it with the first 8000 bytes. If you lookup READTEXT online you will find all you need to understand how to find out the TEXT fields length and read the specified blocks I've outlined in my pseudo-code. (My Field1 = TEXTfield didn't mean to make the Field1 a text field it meant, Field1 = bytes 1 - 8000 of your text field.)

 

[jw2000]

How should the syntax be? Do I need to use varbinary instead of varchar for READTEXT to work?

DECLARE @body1 varbinary(16)
DECLARE @body2 varbinary(16)
DECLARE @body3 varbinary(16)

How can I do this without creating #TempTable, or do I need to create #TempTable?

It seems I need to use a table or temp table and then use READTEXT recordname @varible @start @end as in the following example:

DECLARE @ptr varbinary(16)
SELECT @ptr=TEXTPTR(Notes)
FROM Employees
WHERE LastName = 'Callahan'
READTEXT Employees.Notes @ptr 23 44

 

[jw2000]

Can anyone help?

 

[druer]

I was able to do the following:

declare @Chunk1 varchar(8000)
declare @Chunk2 varchar(8000)
declare @Chunk3 varchar(8000)
select @Chunk1 = Substring(YourTextField, 1, 8000)
select @Chunk2 = Substring(YourTextField, 8001, 8000)
select @Chunk3 = Substring(YourTextfield, 16001, 8000)

I didn't realize that you could just substring on a TEXT field, but I found documentation that suggested that you could in deed, and when I tested I was successfull. (My TEXT field wasn't really 24,000 big. I simply adjusted the numbers)

I would probably be sure you did use the @@TEXTSIZE method to see if you really need to read the 2nd or 3rd chunks.

 

[jw2000]

Druer,

I can't use a text field (ie. YourTextField) in a stored procedure because SQL server does not allow the use of TEXT datatype for local variables.

How can I get around this?

 

[druer]

JW

I think I need to give up on this one. One of us is very, very lost. Per your initial request

so @body can be TEXT and allocate the @body value into three varchar (8000) variable

I assumed you had a TEXT field in some table that you wanted broken up into 3 individual VARCHAR fields of 8000 each. My code takes a TEXT field and breaks it into 3 separate varchar fields of 8000 each. The word "YourTextField" means ... YOUR text field, the TEXT field you have in YOUR real field. You never gave a table name where it was coming from, and you never gave a field name so I have no choice but to substitute the generic YOUR TEXT FIELD.

 

[jw2000]

Druer,

I apologize for any confusion and I appreciate your help so far. Thank you!