SQL查询无法正常工作

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

有一个表类别（ID，标题，描述，parentID，friendlyUrl，categoryTypeID）。字段值parentID可以为null。

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

如果@ParentID = null，如何选择只有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).

如果@ParentID =指定的int值（它是正确的），此查询将选择所有具有parentID = @ ParentID的行。

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

但是如果@ParentID = null，它会选择所有行（它不对）。

2 个解决方案

#1

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.

当@ParentID == null时，该等式的第一部分为真，并且由于OR语句，布尔逻辑的第二部分不再重要并被忽略。

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

这就是AdaTheDev的答案适用于@ID For @ParentID的原因，你需要：

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

#2

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)

select * from Category where（@ID为null或ID = @ID）和（@FriendlyUrl为null或FriendlyUrl = @ FriendlyUrl）和（NOT（@ ParentID为null）或ParentID = @ ParentID）'在此处添加并且（@ CategoryTypeID为null或CategoryTypeID = @ CategoryTypeID）

#1