SQL Server: ISNULL on uniqueidentifier
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.
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.
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)
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 null
s:
WHERE (@myvar IS NULL AND col1 IS NULL) OR (col1 = @myvar)