Return single or multiple records in SP-CodePudding

I have an SP which has a parameter @itemCode. If value of parameter is NULL, the SP should return all items records in the table and if the parameter has value then the SP should only return the respective itemcode record. This is what I have written:

SELECT
    itemCode
    , itemName
FROM itemTable
WHERE itemCode IN (ISNULL(@itemCode, (SELECT itemCode FROM itemTable)))

The query is returning below error message not sure why because I am already using itemCode IN and not itemCode = in WHERE clause. I am using SQL Server.

Can anyone please suggest the problem or give any alternate solution? Thanks.

CodePudding user response：

I found a solution, but not sure if this is a perfect one. Just changed below in WHERE clause

WHERE itemCode LIKE (ISNULL(@itemCode, '%'))

CodePudding user response：

Just use OR logic, pretty much a direct translation of your explanation e.g.

SELECT
    ItemCode
    , ItemName
FROM ItemTable
WHERE ItemCode = @ItemCode OR @ItemCode IS NULL;