How to check ISNULL in Where clause for query?-CodePudding

I have a query like:

    SELECT
        PLIR.ListItemID AS [value],
        Item.ActiveFlag,
        Item.ListItemCode
    FROM ListItem AS [Item] INNER JOIN ListItemRelationship AS [PLIR] ON Item.ListItemID = PLIR.ListItemID
    WHERE Item.ListCode = @ListCode
        AND Item.ListItemLongValue LIKE ISNULL('%' @mask '%', NULL)
        AND Item.ListItemID IN (SELECT ITEMS FROM dbo.Split(@id, '|'))

In the last line, my @id parameter can be null. How can I check the NULL for the @id parameter?

CodePudding user response：

Use IS NULL with OR:

WHERE Item.ListCode = @ListCode
AND Item.ListItemLongValue LIKE ISNULL('%' @mask '%', NULL)
AND (
  @id IS NULL
  OR Item.ListItemID IN (SELECT ITEMS FROM dbo.Split(@id, '|'))
)