SA0251 : Subquery used in expression not ensured to return a single value

The topic describes the SA0251 analysis rule.

Message

Subquery used in expression not ensured to return a single value

Description

The rule checks T-SQL code for subqueries appearing in expression, which returns more than one column, doesn’t have a TOP 1 clause .

If subquery appears in an expression, it must return a single value. Such subquery must return a single column and be ensured to return an single row using the TOP 1 clause.

Queries with select list having aggregate functions without group by clause will be considered to return a single row.

How to fix

To ensure a single value from the subquery, add a TOP (1) clause to it, and make sure that there is a single column in its select list.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1SELECT * FROM Table1
 2WHERE Col1 = ( SELECT Col1,Col2 FROM Table2)
 3
 4SELECT t.* FROM Table1 t
 5WHERE Col1 = ( SELECT * FROM Table2 t2) OR Col2 >= ( SELECT t2.* FROM Table2 t2)
 6
 7SELECT (SELECT Col1 FROM Table2) AS col1 FROM Table1
 8
 9
10SELECT t.* FROM (SELECT * FROM Table1) t
11
12SELECT Name
13FROM Production.Product
14WHERE ProductSubcategoryID = ANY
15    (SELECT ProductSubcategoryID,Name
16     FROM Production.ProductSubcategory
17     WHERE Name = 'Wheels');
18SELECT Name
19FROM Production.Product
20WHERE ProductSubcategoryID IN
21    (SELECT ProductSubcategoryID
22     FROM Production.ProductSubcategory
23     WHERE Name = 'Wheels');
24
25SELECT Name
26FROM Production.Product
27WHERE ListPrice >
28    (SELECT MIN (ListPrice)
29     FROM Production.Product
30     GROUP BY ProductSubcategoryID
31     HAVING ProductSubcategoryID = 14);
32SELECT Name
33FROM Production.Product
34WHERE ListPrice >
35    (SELECT MIN (ListPrice)
36     FROM Production.Product);
37SELECT CustomerID
38FROM Sales.Customer
39WHERE TerritoryID =
40    (SELECT TerritoryID
41     FROM Sales.SalesPerson
42     WHERE BusinessEntityID = 276);
43
44SELECT Name
45FROM Production.Product
46WHERE EXISTS
47    (SELECT * 
48     FROM Production.ProductSubcategory
49     WHERE ProductSubcategoryID = 
50            Production.Product.ProductSubcategoryID
51        AND Name = 'Wheels');
52
53UPDATE                #TmpPosHead
54SET                        IsPending = 1
55                        , OwnPrsResp_ID = (SELECT TOP 1 PrsOsp_ID FROM dbo.v_nom_ContragentsInt WHERE ID = #TmpPosHead.Cg_ID)
56                        , CgPrsMol_ID = (SELECT TOP 1 ID FROM dbo.v_nom_Persons WHERE Cg_ID = #TmpPosHead.Cg_ID ORDER BY IsDefMol DESC, Name)
57                        , CgPrsResp_ID = (SELECT TOP 1 ID FROM dbo.v_nom_Persons WHERE Cg_ID = #TmpPosHead.Cg_ID ORDER BY IsDefOsp DESC, Name)
58                        , CgPrsReceive_ID = (SELECT TOP (1) ID FROM dbo.v_nom_Persons WHERE Cg_ID = #TmpPosHead.Cg_ID ORDER BY IsDefMol DESC, Name)
59--                        , State = 2
60WHERE                DocNo IS NULL
Analysis Results
  Message Line Column
1 SA0251 : Subquery used in expression not ensured to return a single value. 2 15
2 SA0251 : Subquery used in expression not ensured to return a single value. 5 15
3 SA0251 : Subquery used in expression not ensured to return a single value. 5 53
4 SA0251 : Subquery used in expression not ensured to return a single value. 7 8
5 SA0251 : Subquery used in ANY expression is not returning a single column. 15 5
6 SA0251 : Subquery used in expression not ensured to return a single value. 28 5
7 SA0251 : Subquery used in expression not ensured to return a single value. 40 5
See Also

Other Resources

Analysis Rules