How to use comma separated columns in CASE WHEN Statement? I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check and advice why its not working?

WHERE supplierAccountDetail.SupplierMasterID IN (
        CASE 
            WHEN @SupplierMasterID IS NOT NULL
                THEN @SupplierMasterID
            ELSE (sales.ParentSupplierMasterID
                ,sales.DivisionSupplierMasterID) //Error - Syntax Error
            END
        )
If you want to keep the CASE WHEN statements then you have to specify a new CASE WHEN statement per column / value in the IN() statement.

Example:

    WHERE supplierAccountDetail.SupplierMasterID 
    IN(
    CASE 
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.ParentSupplierMasterID END
    ,
    CASE 
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.DivisionSupplierMasterID END);


This does mean that you will compare your column to the variable twice.

Alternatively you could change the query to what Akina mentioned in the comments:

WHERE (supplierAccountDetail.SupplierMasterID  = @SupplierMasterID AND  @SupplierMasterID IS NOT NULL)
OR 
(
@SupplierMasterID IS NULL 
AND
(
 supplierAccountDetail.SupplierMasterID IN(sales.ParentSupplierMasterID,sales.DivisionSupplierMasterID) )
);