title: WHERE CASE WHEN statement with Exists
body: <p>I am creating a SQL query having <code>WHERE CASE WHEN</code> statement. I am doing something wrong and getting error. </p>
  
  <p>My SQL statement is like</p>
  
  <pre><code>DECLARE @AreaId INT = 2
  DECLARE @Areas Table(AreaId int)
  
  INSERT INTO @Areas SELECT AreaId
  FROM AreaMaster
  WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)
  
  SELECT * 
  FROM dbo.CompanyMaster
  WHERE AreaId IN
       (CASE WHEN EXISTS (SELECT BusinessId
                          FROM dbo.AreaSubscription
                          WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
               THEN @AreaId 
               ELSE (SELECT [@Areas].AreaId FROM @Areas)
        END)
  </code></pre>
  
  <p>I am getting error as </p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Line 11<br>
    Subquery returned more than 1 value. This is not permitted when the subquery
    follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an
    expression.</p>
  </blockquote>
  
  <p>Please help to successfully run query. My logic is to checking for conditional <code>AreaId</code> in (statement) for each row.</p>
  
  <p>I want to select the row only when </p>
  
  <ol>
  <li>company has subscription entry into <code>AreaSubscription</code> for specific area passed by <code>@AreaId</code> </li>
  <li>table <code>AreaSubscription</code> does not have subscription entry then evaluate <code>AreaId</code> in <code>(SELECT [@Areas].AreaId FROM @Areas)</code></li>
  </ol>