SQL 2005 Error - The data types varchar and text are incompatible in the add operator.

I replaced  pc.Description with CASE WHEN pc.Description = '**long**' THEN pc.DescriptionLong ELSE pc.Description END AS Description and it threw this error at me. Any suggestions how to fix this?

Msg 402, Level 16, State 1, Procedure cusfn_CorrNotes, Line 8
The data types varchar and text are incompatible in the add operator.

USE [DemoCHC]
GO
/****** Object:  UserDefinedFunction [dbo].[cusfn_CorrNotes]    Script Date: 03/28/2010 10:27:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cusfn_CorrNotes] ( @PatientVisitId INT ) 
RETURNS VARCHAR(8000) 
AS BEGIN 
 
DECLARE 
@Result VARCHAR(8000);  
 
SELECT @Result = (
  SELECT ', (' + ISNULL(Convert(VarChar(20), pc.Created, 101),'') + 
    ' ' + RTRIM(pc.CreatedBy) + ') - ' +
    CASE WHEN pc.Description = '**long**' THEN pc.DescriptionLong ELSE pc.Description END AS Description
  FROM PatientCorrespondence pc 
  WHERE pc.PatientVisitID = @PatientVisitId 
    AND Description > '' 
  ORDER BY pc.Created DESC
  FOR XML PATH(''));
RETURN  Stuff(@Result, 1, 2, '');
 
END

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
So now you are receiving correct results it seems, earlier you haven't received correct result sets..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this:
USE [DemoCHC]
GO
/****** Object:  UserDefinedFunction [dbo].[cusfn_CorrNotes]    Script Date: 03/28/2010 10:27:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cusfn_CorrNotes] ( @PatientVisitId INT ) 
RETURNS VARCHAR(8000) 
AS BEGIN 
 
DECLARE 
@Result VARCHAR(8000);  
 
SELECT @Result = (
  SELECT ', (' + ISNULL(Convert(VarChar(20), pc.Created, 101),'') + 
    ' ' + RTRIM(pc.CreatedBy) + ') - ' +
    CASE WHEN pc.Description = '**long**' THEN cast(pc.DescriptionLong as VARCHAR(8000)) ELSE cast(pc.Description as VARCHAR(8000)) END AS Description
  FROM PatientCorrespondence pc 
  WHERE pc.PatientVisitID = @PatientVisitId 
    AND Description > '' 
  ORDER BY pc.Created DESC
  FOR XML PATH(''));
RETURN  Stuff(@Result, 1, 2, '');
 
END

Open in new window

0
 
Anthony PerkinsCommented:
Is there any chance you can change the deprecated text data type to varchar(MAX)?  If so that would be a better solution.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jeff SAuthor Commented:
acperkins,
Should I change VARCHAR(8000) to VARCHAR(MAX)? When I select VARCHAR(MAX), how many characters does this give me over the 8000? rrjegan17 coding worked like a charm, just now considering if I need to use MAX vs 8000 now. Thoughts?
0
 
Anthony PerkinsCommented:
>>Should I change VARCHAR(8000) to VARCHAR(MAX)?<<
No, I said text to varchar(MAX)

>> how many characters does this give me over the 8000? <<
2 billion - 8000.

>>coding worked like a charm, just now considering if I need to use MAX vs 8000 now<<
Only if you have values that are longer than 8000.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> just now considering if I need to use MAX vs 8000 now. Thoughts?

Agree with acperkins comments above..
Text datatype is going to be deprecated in future versions and recommended by microsoft to use varchar(max) instead of text datatype.

And if you would be storing max of 8000 characters, then have the datatype as varchar(8000) alone..
0
 
Jeff SAuthor Commented:
rrjegan17 or acperkins:
I changed to varchar(MAX). Should I be changing this new section from varchar(8000) to varchar(max)?

CASE WHEN pc.Description = '**long**'
THEN CAST(pc.DescriptionLong AS VARCHAR(8000))
ELSE CAST(pc.Description AS VARCHAR(8000))
END AS Description

USE [DemoCHC]
GO
/****** Object:  UserDefinedFunction [dbo].[cusfn_CorrNotes]    Script Date: 03/29/2010 08:43:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cusfn_CorrNotes] ( @PatientVisitId INT )
RETURNS VARCHAR(MAX)
AS BEGIN  
  
    DECLARE @Result VARCHAR(MAX) ;   
  
    SELECT  @Result = ( SELECT  ', (' + ISNULL(CONVERT(VARCHAR(20), pc.Created, 101),
                                               '') + ' ' + RTRIM(pc.CreatedBy)
                                + ') - '
                                + CASE WHEN pc.Description = '**long**'
                                       THEN CAST(pc.DescriptionLong AS VARCHAR(8000))
                                       ELSE CAST(pc.Description AS VARCHAR(8000))
                                  END AS Description
                        FROM    PatientCorrespondence pc
                        WHERE   pc.PatientVisitID = @PatientVisitId
                                AND Description > ''
                        ORDER BY pc.Created DESC
                      FOR
                        XML PATH('')
                      ) ; 
    RETURN STUFF(@Result, 1, 2, '') ; 
  
   END

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Should I be changing this new section from varchar(8000) to varchar(max)?

Not required..
Conversion between Datatypes varchar(8000) and varchar(max) are implicit and hence you don't need to CAST it..
0
 
Jeff SAuthor Commented:
rrjegan17
With you changes, I get some really odd results. Should I be trimming this? This is what I get as a sample output with your changes:
escription>, (05/12/2006 Owner) - Printed 60 Day Letter</Description><Description>, (01/31/2004 System) - Visit Transferred to Collection</Description><Description>, (07/24/2000 System) - Visit Removed from Collection</Description><Description>, (06/16/2000 System) - Visit Transferred to Collection</Description><Description>, (03/21/2000 System) - Visit Removed from Collection</Description><Description>, (03/18/2000 System) - Visit Transferred to Collection</Description>
This is the same visit with my prior coding:
 (05/12/2006 Owner) - Printed 60 Day Letter, (01/31/2004 System) - Visit Transferred to Collection, (07/24/2000 System) - Visit Removed from Collection, (06/16/2000 System) - Visit Transferred to Collection, (03/21/2000 System) - Visit Removed from Collection, (03/18/2000 System) - Visit Transferred to Collection
Not sure why I am getting all the extra in yours. Suggestions?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Seems like your earlier code trimmed it down to some lower no. of characters..
Kindly select these two additional columns pc.DescriptionLong and pc.Description in the below select query to confirm
SELECT  ', (' + ISNULL(CONVERT(VARCHAR(20), pc.Created, 101),
                                               '') + ' ' + RTRIM(pc.CreatedBy)
                                + ') - '
                                + CASE WHEN pc.Description = '**long**'
                                       THEN CAST(pc.DescriptionLong AS VARCHAR(8000))
                                       ELSE CAST(pc.Description AS VARCHAR(8000))
                                  END AS Description,
pc.DescriptionLong, pc.Description
                        FROM    PatientCorrespondence pc
                        WHERE   pc.PatientVisitID = @PatientVisitId
                                AND Description > ''
                        ORDER BY pc.Created DESC

Open in new window

0
 
Jeff SAuthor Commented:
It gave me this as a result:
, (05/12/2006 Owner) - Printed 60 Day Letter
, (01/31/2004 System) - Visit Transferred to Collection
, (07/24/2000 System) - Visit Removed from Collection
, (06/16/2000 System) - Visit Transferred to Collection
, (03/21/2000 System) - Visit Removed from Collection
, (03/18/2000 System) - Visit Transferred to Collection
0
 
Anthony PerkinsConnect With a Mentor Commented:
If you have changed the data type to varchar(MAX) then your original query should not error out.  I would however make some minor changes:
ALTER FUNCTION [dbo].[cusfn_CorrNotes] (@PatientVisitId INT)
RETURNS VARCHAR(MAX)
AS 
BEGIN  
  
    DECLARE @Result VARCHAR(MAX) ;   
  
    SELECT  @Result = (SELECT   ', (' + ISNULL(CONVERT(varchar(10), pc.Created, 101), '') + ' ' + 
								RTRIM(pc.CreatedBy) + ') - ' +
                                CASE pc.[DESCRIPTION]
									WHEN  '**long**' THEN pc.DescriptionLong
									ELSE pc.[Description]
								END [Description]
                       FROM     PatientCorrespondence pc
                       WHERE    pc.PatientVisitID = @PatientVisitId
                                AND pc.[Description] <> ''
                       ORDER BY 
								pc.Created DESC
                      FOR XML PATH('')
                      );
    RETURN STUFF(@Result, 1, 2, '') ; 
  
END

Open in new window

0
 
Jeff SAuthor Commented:
Thanks. Split points to be fair.
0
All Courses

From novice to tech pro — start learning today.