0

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
        )
1
  • 1
    WHERE ( @SupplierMasterID IS NOT NULL AND supplierAccountDetail.SupplierMasterID IN ( @SupplierMasterID ) ) OR ( @SupplierMasterID IS NULL AND supplierAccountDetail.SupplierMasterID IN (sales.ParentSupplierMasterID, sales.DivisionSupplierMasterID) )
    – Akina
    Oct 14, 2019 at 12:40

1 Answer 1

1

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) )
);
1
  • 1
    An alternative for CASE: IN (COALESCE(@SupplierMasterID, sales.ParentSupplierMasterID), COALESCE(@SupplierMasterID, sales.DivisionSupplierMasterID)) Oct 14, 2019 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.