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 ProductNumber 
    WHEN (SELECT COUNT(*) FROM Customer) > 100 THEN 'Blue'  --Non compliant code (Clause containing sub queries)
    WHEN 200 THEN 'Red'
    ELSE
		'Black'
  END,
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 ProductNumber 
    WHEN @customer > 100 THEN 'Blue' --Compliant code 
    WHEN 200 THEN 'Red'
    ELSE
		'Black'
  END,
FROM Production.Product;