First this type of error is because a subquery returned more than one row when it wasn’t allowed to. In some cases (setting the result to a variable or doing an equality (=) for example) a subquery can only return one row. Because this is a error that specifically mentions subqueries I guess we should be looking for subqueries. Let’s assume that the error came from a stored procedure although the process will work just as well for any piece of code. Possibly with a few changes.

Setup

I’m using AdventureWorks2014 as my sample database. And of course it was correctly written, so errors like this aren’t going to happen. So let’s write some less than perfect code as a setup.

UPDATE Sales.ShoppingCartItem SET ProductID = 827 
    WHERE ShoppingCartItemID = 4;
GO
CREATE FUNCTION [dbo].[ufnGetStock_Oops](@ProductID [int])
RETURNS [int] 
AS 
BEGIN
    DECLARE @ret int;
    
    SELECT @ret = (SELECT p.[Quantity] 
		FROM [Production].[ProductInventory] p 
		WHERE p.[ProductID] = @ProductID )
    
    IF (@ret IS NULL) 
        SET @ret = 0
    
    RETURN @ret
END;
GO
CREATE PROCEDURE [dbo].[uspBigOleOops] AS
SELECT ProductID, ShoppingCartItemID, ShoppingCartID, Quantity,
	dbo.ufnGetStock_Oops(ProductID) AS StockCount, 
	dbo.ufnGetProductListPrice(ProductID, GetDate()) AS ListPrice
FROM Sales.ShoppingCartItem;
GO

Step 1

The first step is to find any subqueries. Taking a look in the SP uspBigOleOops we don’t find any, but if there had been we would go on to step 2 (and come back once done). Next we need to look at any code that is referenced by the SP. Not a big deal in this example but in a multi page SP it could get a bit trickier. The easiest way to get a list of all of the code called by an object (or trigger) is to use the system SP sys.dm_sql_referenced_entities. Side note: there is also a system sp sys.dm_sql_referencing_entities that let’s you find any code that calls an object.

SELECT * 
FROM sys.dm_sql_referenced_entities('dbo.uspBigOleOops', 'OBJECT');

Unfortunately this also pulls all of the user tables which we don’t need right now. So we add a bit to the query in order to exclude them.

SELECT DISTINCT OBJECTPROPERTYEX(referenced_id, 'BASETYPE'), 
       referenced_database_name, referenced_schema_name, 
       referenced_entity_name 
FROM sys.dm_sql_referenced_entities('dbo.uspBigOleOops', 'OBJECT')
WHERE OBJECTPROPERTYEX(referenced_id, 'BASETYPE') <> 'U';

This returned the following list.

I pulled the code from the first one and nothing, on the second I did find a subquery (look back in the setup). You will also want to run the above query on each of the pieces of code listed to be sure they are not calling additional functions, SPs etc. As you find subqueries move on to Step 2. Once finished with Step 2 come back to Step 1 to continue looking for additional subqueries.
 

Step 2

Here is the first (and in this case only) subquery in the code.

    SELECT @ret = (SELECT p.[Quantity] 
		FROM [Production].[ProductInventory] p 
		WHERE p.[ProductID] = @ProductID )

Setting a variable to the result of a subquery is one of those cases where the subquery can only have one row so we need to continue on. Now that I have found a possible culprit I convert it somewhat to check for possible duplicate rows. (You probably will want to fix code like this regardless, to avoid possible errors in the future. But right now we are looking for the specific cause of the error.) I converted the subquery to the following query to pull any value for the parameter (@ProductID) that will return more than one row. The subquery might also be of a type that doesn’t use parameters but has references to an outer query. In that case you do the grouping based on the columns from the outer query.

SELECT p.[ProductID]
FROM [Production].[ProductInventory] p 
GROUP BY p.[ProductID]
HAVING COUNT(1) > 1

This returned the following:

Any time the ProductID is in that list then this particular function is going to return the subquery error. This is a problem and needs to be resolved by either changing the code or the data. However it might not be the only problem so if there is any code that has not been reviewed yet you need to go back to Step 1.

Now this is not an exhaustive method. It’s not going to find problems in triggers for example. It is however a good start and hopefully will catch 90% of the issues. I do want to point out that once you have found the problem you have two choices. You can either fix the data (if it’s incorrect) or fix the code (if it’s incorrect) or both. If your system is set up correctly this shouldn’t ever been an issue. But let’s face it, who hasn’t found problem code in their systems? Worst case you can always blame it on the last guy who left, right?