Declaring Text Data as a local variable

Question

Declaring Text Data as a local variable

penny calvert

SSC Enthusiast

Points: 173
More actions
June 23, 2004 at 4:35 pm

#84826

There is an existing (called) Stored Procedure (in SQL Server 7.0)- it accepts a TEXT variable as an input data type. However, the calling procedure cannot have a TEXT variable that is passed to the 'called' procedure. Also - cannot declare a local variable as data type "TEXT"- why ??

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

It just isn't allowed. Text can be used as a datatype only for SP variables for the input/output (really only as input) but you cannot set inside the SP itself. Only the calling application can use (this allows full flexibility for SP when such fields are invovled). Not sure ultimately why this is so except that this is how MS currently supports it.

sscbm21 Old Hand Points: 323 More actions · Answer 2

You can have a round about using table vairable to store the text data e.g.,

DECLARE @tt TABLE(

TextData text

)

INSERT INTO @tt VALUES('Sample Test')

bm21

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

SQL Server 2000 allows the text, ntext and image data types to hold up to 2GB of data. Because such a huge amount of data can be put in there, sql server treats them differently. Their data is not store in the table rows, like normal columns are, rather a pointer to the data pages that hold the data is stored. (kinda like a FAT entry or how leaf nodes refer to children).

If you go sticking up to 2GB in a variable, SQL Server and your OS are going to run outta memory pretty damn quick (just imagine 4 or more people running the query, yikes!). Thats why you have to play pointer games. The data can stay on disk, and the pointer can be passed around.

from BOL (Managing ntext, text, and image Data):

Unless the text in row option is specified, text, ntext, or image strings are stored outside a data row; only the text pointers to these strings reside in the data rows. Text pointers point to the root node of a tree built of internal pointers that map to the pages in which string fragments (of text, ntext, and image data) are actually stored.

If you want to play with the text pointers, and pass them around in your sprocs, remember that pointers are only valid for the transaction they are in. Checkout textptr in BOL.

Julian Kuiters
juliankuiters.id.au

Rob Hershfield SSC-Addicted Points: 440 More actions · Answer 4

Using a table variable is a clever workaround. However, I treid unsuccessfully to use WRITETEXT and UPDATETEXT with a table variable. Does anyone know whether there is a way to accomplish manipulating an ntext field in a table variable using WRITETEXT and UPDATETEXT or by some other method?

I suppose using a temp table is the next best thing.

Rob H

lost in SQL SSC Enthusiast Points: 163 More actions · Answer 5

Hi Rob

I am facing same problem. If you get any hit or clue, please mail me at bhimani2@gamil.com.

Thanks....

Rob Hershfield SSC-Addicted Points: 440 More actions · Answer 6

Sorry, that post was 3 and 1/2 years ago (about 40+ projects ago) and I have no recollection of what I was working on at the time.

-Rob