SQL Server: ISNULL on uniqueidentifier

Question

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 1

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)

Answer 2

Since the first argument you are passing isnull is not a literal null, it will determine the return type of that call, a uniqueidentifier in your case. The second argument, '', cannot be cast to this type, hence the error you're getting.

One way around this is just to explicitly check for nulls:

WHERE (@myvar IS NULL AND col1 IS NULL) OR (col1 = @myvar)