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!!
ParentId = NULLThis question is related to
sql-server
sql-server-2005
uniqueidentifier
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)
Starting with Sql Server 2012: string concatenation function CONCAT converts to string implicitly. Therefore, another option is
SELECT Id AS 'PatientId',
CONCAT(ParentId,'') AS 'ParentId'
FROM Patients
CONCAT converts null values to empty strings.
Some will consider this hacky, because it merely exploits a side effect of a function while the function itself isn't required for the task in hand.
Select ID, IsNull(Cast(ParentID as varchar(max)),'') from Patients
This is needed because field ParentID is not varchar/nvarchar type. This will do the trick:
Select ID, IsNull(ParentID,'') from Patients
You need to CAST the ParentId as an nvarchar, so that the output is always the same data type.
SELECT Id 'PatientId',
ISNULL(CAST(ParentId as nvarchar(100)),'') 'ParentId'
FROM Patients
Source: Stackoverflow.com