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.

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.

Categories

Design Rules, Bugs

Additional Information
Example Test Script
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