2

I have a simple hierarchical table. Let's say it has these columns:

Id, [Guid], ParentId, Title

Only ParentId is nullable. Other columns are not nullable.

I have created a recursive query to show me the path from the node up to the root. I have used CTE for this purpose. And this is my query:

with Anchor (Id, [Guid], ParentId, Title, [Path])
as
(
    select Id, [Guid], ParentId, Title, cast('' as nvarchar(100)) as [Path]
    from Hierarchies
    where ParentId is null
    union all
    select Hierarchies.Id, Hierarchies.[Guid], Hierarchies.ParentId, Hierarchies.Title, cast(Anchor.[Path] + '/' + Hierarchies.Title as nvarchar(100)) as [Path]
    from Hierarchies
    inner join Anchor
    on Hierarchies.ParentId = Anchor.Id
)
select 
    isnull(Anchor.Id, 0) as Id,
    isnull(Anchor.[Guid], newid()) as [Guid],
    Anchor.ParentId,
    Anchor.Title,
    Anchor.[Path]
from Anchor

The problem is that, when I turn this query into a view, the Guid column becomes nullable. I even used isnull() function to force it not to be null. And to my surprise, that isnull actually worked for the Id column. But it's not working for Guid.

What should I do? Why SQL Server has this behavior?

0

1 Answer 1

5

The NEWID() function is not known for certain by the compiler to always return a value (even though you and I know that), so the only way to fix this is to use ISNULL again

ISNULL(ISNULL(Anchor.[Guid], NEWID()), CAST(0x0 AS uniqueidentifier))

The last parameter never actually gets executed.

Obviously, if you don't actually need NEWID() then you can just use the CAST:

ISNULL(Anchor.[Guid], CAST(0x0 AS uniqueidentifier))
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.