CODEWITHSUNDEEP
sqlsql-servernulluniqueidentifierisnull
TVogt
TVogt

Reputation: 185

SQL Server: ISNULL on uniqueidentifier

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?

Upvotes: 13

Views: 56411

Answers (7)

Sam Salim
Sam Salim

Reputation: 2372

I needed something similar on a where clause to compare 2 fields. Declaring a uniqueidentifier variable is causing performance issues.

So I've used something like this.

WHERE COALESCE(Table1.Field1, CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))=COALESCE(Table2.Field2, CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))

Upvotes: 1

dasarghya
dasarghya

Reputation: 31

Here is another way to overcome this issue: 

DECLARE @myvar uniqueidentifier = NEWID()

SELECT * FROM TABLE
Where ISNULL(col1,@myvar) = ISNULL(Col2,@myvar)

This will resolve your error. Conversion failed when converting from a character string to uniqueidentifier.

Upvotes: 3

NidhinSPradeep
NidhinSPradeep

Reputation: 1304

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)

Upvotes: 17

Sanjeev Gaur
Sanjeev Gaur

Reputation: 21

Try the following code: 

WHERE ISNULL([Guid], NEWID()) = @myvar

Upvotes: 2

Emacs User
Emacs User

Reputation: 1475

As others have pointed out, exclude the NULL values from the results and THEN do the comparison. You can use COALESCE to exclude NULL values from comparisons.

Upvotes: 2

Amit
Amit

Reputation: 46323

The reason ISNULL isn't working for you is that the replacement value (the value to be used if the check expression really is null) must be implicitly convertible to the type of the check expression.

Your WHERE clause can use a col IS NULL AND @var IS NULL to check that state.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311123

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)

Upvotes: 14

Related Questions