Menu
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 = NULL 
756
In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL. SQL Server converts the data type of replacement to data type of expression.
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.
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)
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
