Home

Rule description

• COALESCE, IIF, and CASE input expressions should not contain subqueries

Non-compliant Code Example

SELECT 
COALESCE((SELECT Color, CustomerNumber FROM Customer), 'Blue'),      --Non compliant code (Clause containing sub queries)
IIF((SELECT COUNT(*) FROM Customer) < 100, 'Blue', 'Red'),       --Non compliant code (Clause containing sub queries)
CASE  
    WHEN (SELECT COUNT(*) FROM Customer) < 100 THEN 'Blue'       --Non compliant code (Clause containing sub queries)
    WHEN (SELECT COUNT(*) FROM Customer) >= 100 THEN 'Red'       --Non compliant code (Clause containing sub queries)
    ELSE
        'Black'
    END As CaseResult
FROM Production.Product;

Compliant Code Example

Declare @customer INT;
SELECT  @customer = COUNT(*) FROM Customer;

SELECT 
COALESCE(Color, CustomerNumber),     --Compliant code 
IIF(@customer < 100, 'Blue', 'Red'),     --Compliant code 
CASE  
    WHEN @customer < 100 THEN 'Blue'     --Compliant code 
    WHEN  @customer >= 100 THEN 'Red'    --Compliant code 
    ELSE
        'Black'
    END As CaseResult
FROM Production.Product;