SQL Server: ISNULL on uniqueidentifier

sql sql-server null uniqueidentifier isnull
TVogt picture TVogt · Aug 29, 2015 · Viewed 31.5k times · Source

I am trying to compare a column col1 and a variable @myvar in a WHERE clause. Both usually contain GUIDs, but may also have NULL values. I thought I could get around the fact that NULL=NULL evaluates to FALSE by using WHERE ISNULL(col1, '')=ISNULL(@myvar, ''). That would compare two empty strings instead, and evaluate to TRUE.

This will, however, produce the following error message:

Msg 8169, Level 16, State 2, Line 3 Conversion failed when converting from a character string to uniqueidentifier.

I tried

DECLARE @myvar uniqueidentifier = NULL
SELECT ISNULL(@myvar,'') as col1

Same error message.

Two questions: First, I am trying to convert a uniqueidentifier variable - even though it has a NULL value - to an (empty!) string, not the other way around, as the error message suggests. What gives?

Second, is there a better way to word that WHERE clause I need, to allow for comparing uniqueidentifiers that might be NULL?

Answer

NidhinSPradeep picture NidhinSPradeep · Mar 16, 2017

I think below expression can be used to check if the GUID column is empty 

CAST(0x0 AS UNIQUEIDENTIFIER)

some thing like 

...WHERE GuidId <>  CAST(0x0 AS UNIQUEIDENTIFIER)

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More