Stored procedure to handle null parameter

Question

I'm sure this has a very simple answer I am not finding... I have a simple hierarchy in a database where each row has a ParentId. If ParentId IS NULL, then it's a root element. I have the stored procedure:

CREATE PROCEDURE GetByParent @parentId int
AS BEGIN SELECT * FROM TABLE1 WHERE ParentId = @parentId END

It works fine if I send an integer, but if I send NULL it becomes ParentId = NULL, which doesn't work in ANSI. I know there is COALESCE(@parentId, ParentId), but that returns all rows when @parentId IS NULL. I can do an IF ELSE statement and copy the query (one with = @parentId and the other with IS NULL), but I'm sure there's a better way.

Answer 1

Handle the NULL case in a separate condition:

SELECT *
FROM TABLE1
WHERE ParentId = @parentId
    OR (ParentId IS NULL AND @parentId IS NULL)

Answer 2

you could just use an OR statement

... WHERE (@parentId IS NULL AND ParentId IS NULL) OR ParentId=@parentId

Answer 3

Actually, the IF/ELSE method is the most performant short of using dynamic SQL. Assuming SQL Server 2005+, you'd use:

DECLARE @SQL NVARCHAR(max)
   SET @SQL = 'SELECT * FROM TABLE1 WHERE 1=1 '

   SET @SQL = CASE 
                WHEN @parentid IS NULL THEN ' AND parentid IS NULL '
                ELSE ' AND parentid = @parentid '
              END

BEGIN

  EXEC sp_executesql @SQL, N'parentid INT', @parentid

END

Obligatory IF/ELSE:

BEGIN

  IF @parentid IS NULL
  BEGIN
    SELECT * FROM TABLE1 WHERE parentid IS NULL 
  END
  ELSE
  BEGIN
    SELECT * FROM TABLE1 WHERE parentid = @parentid
  END

END

Answer 4

I'm making the assumption that you are not using negative ids for parentID.

If you use two ISNULL statements, you can cover all the cases in one query:

SELECT * FROM TABLE1 WHERE ISNULL(ParentId,-1) = ISNULL(@parentId,-1)

This way when @parentid IS NULL, it will only return results with null values, instead of all rows.