Sql query doesn't work properly

Question

There is a table Category (ID, title, description, parentID, friendlyUrl, categoryTypeID). The field value parentID can be null.

How do I select rows which have only parentID =null if @ParentID = null.

  declare @ID int =null 
  declare @FriendlyUrl nvarchar(30) = null 
  declare @ParentID int = null 
  declare @CategoryTypeID int = 0


    select * from Category
     where
    (@ID is null or ID = @ID) 
    and (@FriendlyUrl is null or FriendlyUrl=@FriendlyUrl)
    and (@ParentID is null or ParentID=@ParentID) 
    and (@CategoryTypeID is null or CategoryTypeID=@CategoryTypeID)

This query selects all rows which have parentID=@ParentID if @ParentID = specified int value (it's right).

But if @ParentID =null it selects all rows (it's not right).

Answer 1

How about this ... or did I not understand your question properly?

select * from Category
where (@ID is null or ID = @ID)
and (@FriendlyUrl is null or FriendlyUrl=@FriendlyUrl)
and (NOT(@ParentID is null) or ParentID=@ParentID) ' added NOT here and (@CategoryTypeID is null or CategoryTypeID=@CategoryTypeID)

Answer 2

Your error is here:

and (@ParentID is null or ParentID=@ParentID) 

When @ParentID == null the first part of that equasion is true and because of the OR statement the second part of the boolean logic is not important anymore and ignored.

That is the reason AdaTheDev's answer will work for @ID For @ParentID you need:

and ((@ParentID is null AND ParentID is null) or (@ParentID not is null AND ParentID = @parentID))