Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and results were returned.

My question is how do I modify the statement so that I can still filter on those conditions?

Stored Proc code:

SELECT
    REPORT_SPOOL.ID, 
    REPORT_SPOOL.REPORT_SPOOL_TYPE_ID, 
    REPORT_SPOOL.FUND_ID, 
    REPORT_SPOOL.PERF_ENTITY_ID, 
    REPORT_SPOOL.REPORT_GUID, 
    REPORT_SPOOL.REPORT_TEMPLATE_GUID, 
    PERF_ENTITY.CODE AS PERF_ENTITY_CODE, 
    PERF_ENTITY.NAME AS PERF_ENTITY_NAME, 
    FUND.CODE AS FUND_CODE, 
    FUND.NAME AS FUND_NAME, 
    REPORT.CODE AS REPORT_CODE, 
    REPORT.NAME AS REPORT_NAME, 
    REPORT_TEMPLATE.CODE AS REPORT_TEMPLATE_CODE, 
    REPORT_TEMPLATE.NAME AS REPORT_TEMPLATE_NAME,
    FUND.ACCOUNTING_START AS START_DATE,
    FUND.ACCOUNTING_END AS END_DATE
FROM
    PERF_ENTITY 
    RIGHT OUTER JOIN
        REPORT 
        INNER JOIN
            REPORT_SPOOL 
            ON REPORT.GUID = REPORT_SPOOL.REPORT_GUID 
        INNER JOIN
            REPORT_TEMPLATE 
            ON REPORT_SPOOL.REPORT_TEMPLATE_GUID = REPORT_TEMPLATE.GUID 
    ON PERF_ENTITY.ID = REPORT_SPOOL.PERF_ENTITY_ID 
    LEFT OUTER JOIN
    FUND 
    ON REPORT_SPOOL.FUND_ID = FUND.ID
    WHERE 
    (END_DATE IS NULL OR END_DATE &gt; @REPORT_DATE)
    AND 
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] &gt;= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] &lt;= @REPORT_DATE)


Those last 2 SELECTS are the issue
I found the solution.

I replaced the = (equal signs) to IN. 

The WHERE clause now looks like this:

 WHERE 
    (END_DATE IS NULL OR END_DATE &gt; @REPORT_DATE)
    AND 
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] &gt;= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] &lt;= @REPORT_DATE)