CODEWITHSUNDEEP
sql-serverstored-procedures
Fabian Vilers
Fabian Vilers

Reputation: 2982

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

Upvotes: 2

Views: 49

Answers (2)

John Sansom
John Sansom

Reputation: 41819

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

Upvotes: 0

il_guru
il_guru

Reputation: 8508

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

Upvotes: 1

Related Questions