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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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 & Architect, EE Solution GuideCommented:
>> 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 & Architect, EE Solution GuideCommented:
>> 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 & Architect, EE Solution GuideCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
So now you are receiving correct results it seems, earlier you haven't received correct result sets..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.