Home
COALESCE, IIF, and CASE input expressions should not contain subqueries
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;