Identify faulty sub-query (subquery returned more than 1 value) I am trying to resolve an issue with a query that fails with this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

This error isn't detailed enough for me as the query in question has several sub queries of which some have another level of sub queries.

I found the query itself using SQL Server Profiler but I want to know if there is a tool I can use to identify which sub-query this fails at.

The only other way I can think of resolving this is by brute forcing: remove each sub query until the outer query works. However, given the amount of brute forcing I would have to do, it seems like must be a better way to find out.
I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value.

By making sure they all have either:


TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY)
WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496
HAVING clause that uses the primary or a unique key
GROUP BY () or an aggregate function in the HAVING or SELECT clause (this ensures that the subquery will return 1 or 0 rows) 


This of course is not at all trivial with complex queries (example: nested derived tables or ctes and/or nested correlated subqueries) and even harder when the unique keys are not documented.

It's not trivial because for the most complex queries, you may have to figure out the unique keys of all the derived tables/ctes/subqueries which may involve joins and more nested subqueries (and for the queries that involve joins, the foreign key constraints may be needed as well to find the unique keys.)