ntext, text, image variables within sprocs

[LinsLo]

Here are the details of what I am trying to do.

I have a procedure which has a incoming parameter that is an ID. Based on that ID I want to then perform a select statement that will select a field of type text from a table where a column matches with that ID. Then, I want to call a procedure and pass it the text variable which was just obtained. This is where I run into the error that I can not have a local variable of type text, ntext or image. Any ideas? I have tried selecting straight in for the parameter of a procedure but that doesn't work either.


Any suggestions would be greatly appreciated!

Lindsay

 

[NathanGriffiths]

Instead of selecting from the text column perhaps you could use TEXTPTR to return a pointer to the text column and then pass that to the second stored procedure. Inside that stored procedure you could use READTXT to access the text data.

e.g. from BOL;

DECLARE @textpointer varbinary(16)
SELECT @textpointer = TEXTPTR(<text column>) FROM <table> WHERE <id column> = <id>

then pass @textpointer to the stored procedure and inside the procedure do

READTEXT <table>.<text column> @textpointer ....

see BOL for more details.

OR, if the data in your text column is less than 8000 characters maximum you could read it into a VARCHAR variable and pass that to your stored proc e.g.

DECLARE @myvarchar varchar(8000)

SELECT @myvarchar = Substring(<text column>, 0, 8000) FROM ... WHERE ...

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------