Text/Image Datatype in Stored Procedure.

Question

Post reply

Text/Image Datatype in Stored Procedure.

ramesh_kondaparthy

Ten Centuries

Points: 1069
More actions
May 28, 2003 at 7:14 am

#81634

How i can retrive and update a image/text data type in stored procedure?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi ramesh_kondaparthy,

DECLARE @text varchar(8000)

SELECT @text=<your_textfield> FROM <table>

Now you should do your manipulation

At the end there (should?) be a UPDATE <table> SET <textfield> = @text

I'm not quite sure on this for I use only the first two lines and a print @text to see the full content of a textfield

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kk_Avantel SSC Veteran Points: 226 More actions · Answer 2

hi ,

i used the below code u suggested.

DECLARE @text varchar(8000)

SELECT @text=EmpMast_Signature FROM hr_empmast where EmpMast_Code=6

print @text

here EmpMast_Signature is of image datatype

but it is giving error

Operand type clash: image is incompatible with varchar

Operand type clash: image is incompatible with varchar

Bye

Krishna Kumar

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Sorry,

if you are about to retrieve image (binary) data you should replace varchar(8000) with varbinary(8000). This should work.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

winash SSChampion Points: 11446 More actions · Answer 4

w.r.to the text data types in stored procedures - when you use a varchar(8000) local variable and if the length of the text data type exceeds this limit then the data is truncated and stored in the local variable....

an alternative is to use a temporary table...something like

Create table #Temp(TextType text)

Insert #Temp(TextType)

Select TextField from Table

Drop table #Temp

I think the Table datatype could also be used(I use SQL Server 7.0 so I don't know much about the Table datatype)