• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

Help using UPDATETEXT

Hello all,


--SNIPPET--
      if (select max(summarySeqNo) from #tAdvancedSearchResults where id = @v_id) = 2
            BEGIN
                  UPDATE #tAdvancedSearchResults
                  SET
                        summary = CAST(@v_summary as ntext)+ ' ' + (select CAST(summary AS ntext) from #tAdvancedSearchResults
                                                where id = @v_id and summarySeqNo = 2),
                  WHERE
                        id = @tempId
                  
                  DELETE FROM #tAdvancedSearchResults
                  WHERE
                        id = @tempId and
                        summarySeqNo =2
            END
--SNIPPET--
summary is ntext ,v_summary is nvarchar


I get this error when I try to execute a SP with this snippet.
"Invalid operator for data type. Operator equals add, type equals ntext."

Can you guys please provide me with a simple example using UPDATETEXT?
OR
Any other way to concatenate two variables of type ntext.
0
spankenstien
Asked:
spankenstien
  • 2
1 Solution
 
adwisemanCommented:
the problem is here.  

summary = CAST(@v_summary as ntext)+ ' ' + (select CAST(summary AS ntext) from #tAdvancedSearchResults
                                        where id = @v_id and summarySeqNo = 2)


Try

summary = CAST(@v_summary as ntext + ' ' + (select summary from #tAdvancedSearchResults
                                        where id = @v_id and summarySeqNo = 2) as ntext)

You may also get an error that ntext can not be used in a subquery, so you may have to pull the sub query out into a select statement



UPDATE #tAdvancedSearchResults
               SET
                    summary = CAST(@v_summary + ' ' + tmp.summary) as ntext)
               FROM #tAdvancedSearchResults, #tAdvancedSearchResults tmp
               WHERE tmp.id = @v_id and tmp.summarySeqNo = #tAdvancedSearchResults.summarySeqNo AND
                    #tAdvancedSearchResults.id = @tempId
               
               
0
 
spankenstienAuthor Commented:
adwiseman,
I have tried:
1)
                  UPDATE #tAdvancedSearchResults
                  SET
                        summary = CAST(@v_summary as ntext) + '' + CAST((select summary from #tAdvancedSearchResults
                                        where id = @v_id and summarySeqNo = 2) as ntext),
                        summarySeqNo = 0
                  WHERE
                        id = @tempId
"Invalid operator for data type. Operator equals add, type equals ntext."

2)
                  UPDATE #tAdvancedSearchResults
                  SET summary = CAST((@v_summary + '' + tmp.summary) as ntext)
                  FROM #tAdvancedSearchResults, #tAdvancedSearchResults tmp
                  WHERE tmp.id = @v_id and tmp.summarySeqNo = 2 AND
                                #tAdvancedSearchResults.id = @tempId      

"Invalid operator for data type. Operator equals add, type equals ntext."

3)
                  UPDATE a
                  SET a.summary = CAST(@v_summary as ntext) + '' + CAST(tmp.summary as ntext)
                  FROM #tAdvancedSearchResults a inner join #tAdvancedSearchResults tmp on tmp.id = a.id
                  WHERE tmp.summarySeqNo = 2 AND
                        a.summarySeqNo = 1
"Invalid operator for data type. Operator equals add, type equals ntext"

Here is what I am trying to acheive:

id seqNo text(nvarchar 4000 length)
123  1 aaaaaaaa..a4000.
123  2 bbbb....b600

what I need is
123 1 aaaaaaaa......aaabbbb.....


0
 
spankenstienAuthor Commented:
Solution:

      Declare c cursor for
            select distinct id, summary, summarySeqNo from iolwarehouse.dbo.vCustomSearchInt ORDER BY id, summarySeqNo desc
      Open c
      
      Fetch next from c
            into
                  @v_id,
                  @v_summary,
                  @v_summarySeqNo

      WHILE @@FETCH_STATUS = 0
      BEGIN
            
            SELECT @ptrval_summary = TEXTPTR(summary)
            FROM #tIntResults
            WHERE id = @v_id

            if @ptrval_summary is not null
            begin
                  UPDATETEXT #tIntResults.summary @ptrval_summary 0 0 ''
                  UPDATETEXT #tIntResults.summary @ptrval_summary 0 0 @v_summary                    
            end
            else
            begin
                  update #tIntResults
                  set summary = @v_summary
                  WHERE id = @v_id
            end
      Fetch next from c
            into
                  @v_id,
                  @v_summary,
                  @v_summarySeqNo
      END      

      close c
      deallocate c

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now