Convert NULL to empty string - Conversion failed when converting from a character string to uniqueidentifier

Question

Using SQL Server 2005 how do I get the below statement or rather the output as i want it to be.

SELECT Id   'PatientId',
       ISNULL(ParentId,'')  'ParentId'
FROM Patients

ParenId is a uniqueidentifier that allows NULL, but seems that query optimizer tries to also convert '' back to uniqueidentifier for the rows where ParentId = NULL.As the title says that's the exact error info the query runner throws at my face!!

• How do i get the server to return empty string for ParentId = NULL

Answer 1

SELECT Id   'PatientId',
       ISNULL(CONVERT(varchar(50),ParentId),'')  'ParentId'
FROM Patients

ISNULL always tries to return a result that has the same data type as the type of its first argument. So, if you want the result to be a string (varchar), you'd best make sure that's the type of the first argument.

---

COALESCE is usually a better function to use than ISNULL, since it considers all argument data types and applies appropriate precedence rules to determine the final resulting data type. Unfortunately, in this case, uniqueidentifier has higher precedence than varchar, so that doesn't help.

(It's also generally preferred because it extends to more than two arguments)