13

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?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
TVogt
  • 185
  • 1
  • 1
  • 11
  • 1
    Do you want your valid uniqueidentifiers to be converted to strings too? – shawnt00 Aug 28 '15 at 23:13
  • shawnt00, for the WHERE clause the non-NULL uniqueidentifiers don't need to be converted to strings. Comparing them just has to evaluate to TRUE so I only retrieve records where they are equal. – TVogt Aug 29 '15 at 03:19

7 Answers7

16

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)
NidhinSPradeep
  • 1,186
  • 2
  • 14
  • 15
14

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)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 2
    There is a second part important to keep in mind. At the moment you put a column into a function (like ISNULL) you make it impossible for the query optimizer to use its potential. Look at this ("sargable"): http://stackoverflow.com/q/799584/5089204 – Shnugo Aug 28 '15 at 23:17
  • Shnugo, I had not even considered this. This might come to make an important difference, as all this is going to be part of a python program that is going to run this query (or very similar ones) thousands of times in a row (through pyodbc), so any time savings be they ever so small may count! – TVogt Aug 29 '15 at 03:36
  • @Shnugo, having looked into this further, am I right to assume that this applies to user defined functions as well as built-in functions...? – TVogt Aug 31 '15 at 19:10
  • @ThorstenVogt, yes, the biggest deal with performance is to let the optimizer do its work. Non-sargable parts can make queries awfully slow, but you cannot imagine why ... – Shnugo Aug 31 '15 at 19:20
3

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.

xiawi
  • 1,772
  • 4
  • 19
  • 21
dasarghya
  • 31
  • 1
2

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.

Amit
  • 45,440
  • 9
  • 78
  • 110
2

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.

Emacs User
  • 1,457
  • 1
  • 12
  • 19
  • 1
    Look at my comment below @Mureinik. Avoid function calls (COALESCE) for "sargability" – Shnugo Aug 28 '15 at 23:39
  • What would have been better is a sample showing how to convert a non-sargable where clause into a sargable select statement, both using COALESCE or ISNULL to solve the problem. – Emacs User Aug 29 '15 at 01:39
  • COALESCE would solve the problem with the error I get nicely. I will take the added potential performance bonus, though. :o) I only need this for the WHERE clause though, not the SELECT statement. – TVogt Aug 29 '15 at 03:44
  • @EmacsUser, if you follow the link I postet in my comment below Mureinik's answer you'll find plenty of examples. – Shnugo Aug 29 '15 at 08:32
  • @TVogt, Maybe I do not understand you correctly, but it is wrong to hope, that something in the WHERE would not influence the query itself. It is exactly the other way round! Just imagine your "WHERE" filters a table with millions of rows down to just a few. This should probably be the first step. If the optimizer cannot look "into" it, this filter is applied **after** the select. Millions of rows are fetched - and thrown away at the end... – Shnugo Aug 31 '15 at 22:51
  • @Shnugo, no, you explained this quite well. First, the WHERE clause limits which records to fetch - thereby determining the rows; then the SELECT statement tells the database what data to get out of those rows, by choosing the columns. And as I understand it, performance gains are achieved predominantly by limiting the number of rows, more so than the number of columns. Yes? – TVogt Sep 01 '15 at 03:24
  • @TVogt, well, there's not enough space to explain the query optimizer in detail. But in short: Yes, few rows mean little work. The optimizer is much more intelligent actually (read about joins, indexes, statistic, scan vs. seek). There are very few situations, where a developer should influence this (read about hints). Normally it's best not to stand in the way :-) – Shnugo Sep 01 '15 at 05:29
2

Try the following code: 

WHERE ISNULL([Guid], NEWID()) = @myvar
Sarabjit Singh
  • 1,814
  • 1
  • 17
  • 28
Sanjeev Gaur
  • 21
  • 1
  • Welcome to Stack Overflow! Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Feb 13 '17 at 11:47
  • 2
    This still has the problem of treating two nulls as not equal to each other, since NEWID() returns a random GUID, not a blank GUID. – Joey Adams May 18 '17 at 19:07
1

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))
Sam Salim
  • 2,145
  • 22
  • 18