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 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;