Accessing text fields in a stored procedure and insert to another table

Question

Hi,

I am trying to access a “text” type and inserting that value into another table viw a stored procedure. I’ve tried to cast it, convert it, but nothing works.

My code looks somethings like this:

Declare @Critique varchar(max), @Feedback varchar(max)
…
…
…
SELECT @Critique = CAST(comments as varchar(max)), @Feedback = CAST(public_critique as varchar(max)) FROM ASCO_vEXTERNAL_REVIEW_APPLICATIONS_LIST WHERE wf_task_assignment_id = @WfTaskAssignmentIDP1

– comments and public_critique are defined as text in view (also tried with table) ASCO_vEXTERNAL_REVIEW_APPLICATIONS_LIST
…
…
…
insert into WF_TASK_ASSIGNMENT_REVIEW (wf_task_assignment_review_id, wf_task_assignment_id, grantee_project_id, comments, public_critique) values (@NewID1, @WfTaskAssignmentIDP2, @GranteeProjectID, @Critique, @Feedback)

Can you please help me with this as soon as possible. I would really appreciate this.

Thanks, Harish

Answer 1

Your question is not sound good to understand .

Dont use text ,it wont support in many cases like where ,group by etc , so try use varchar

This is just an example

Declare @Critique varchar(max)
set @Critique = (select public_critique  from ASCO_vEXTERNAL_REVIEW_APPLICATIONS_LIST 
 where convert(varchar(50), wf_task_assignment_id ) =@WfTaskAssignmentIDP1)

Answer 2

I'm assuming that the WF_TASK_ASSIGNMENT_REVIEW is the one containing the text column you're trying to write into.

The text type is now deprecated in SQL 2005 and 2008. If at all possible try and upgrade the WF_TASK_ASSIGNMENT_REVIEW table to use the nvarchar(max) type instead.

If not, the only way is to use the WRITETEXT statement to write into the target column, in a loop (since WRITETEXT has an upper limit). See the WRITETEXT statement example in the SQL Server docs.