SP: handling nulls

I have this Table structure:

Id int not null --PK
Title varchar(50)
ParentId int null --FK to same Table.Id

I'm writing a SP that returns a row's "brothers", here's the code

select * from Table
where Table.ParentId = (select Table.ParentId from Table where Table.id = @Id)
and Table.Id <> @Id

It works perfectly for rows having a parent, but for those who's parent are null (root records), it returns no row. This is working as expected since null = null is always false.

I'm looking for help on how to better design my SP to handle this specific case. I'm not a DBA and my TSQL knowledge is basic.

EDIT: I've updated my SQL query like this:

DECLARE @Id INT
SET @Id = 1

DECLARE @ParentId INT
SET @ParentId = (SELECT Table.ParentId FROM Table WHERE Table.Id = @Id)

SELECT * FROM Table
WHERE (
    (@ParentId IS NULL AND (Table.ParentId IS NULL))
    OR (Table.ParentId = @ParentId)
)

AND Table.Id <> @Id

It does do the job but if the Id is not in the table, it still returns the row who have no parents. Going to lunch, 开发者_高级运维continue looking at this later.

Thanks in advance, Fabian

I'm not sure this is the best solution, but you could try to use the COALESCE operator using a "not valid" id for NULL

select * from Table
where COALESCE(Table.ParentId,-1) = (select COALESCE(Table.ParentId,-1) from Table where Table.id = @Id)
and Table.Id <> @Id

Assuming -1 is never used as an ID

It's possible I have not understood your problem description however, in order to return Brothers only when they exist for a given Parent, the following query should suffice:

  select Brother.* 
    from Table Parent
        inner join Table Brother on
          Parent.id = Brother.ParentID
    where Parent.Id= @Id and Brother.Id <> @Id