title: Pass parameter to subquery
body: <p>I'm using SSRS/SSDT in Visual Studio 2015 and SQL Server 2014. There's a bug that's been present for > 8 years where <a href="https://stackoverflow.com/questions/14466874/in-ssrs-why-do-i-get-the-error-item-with-same-key-has-already-been-added-wh">you can't select multiple columns from different tables that have the same name</a>. To get around this, I need to use a subquery. Every single answer I find rewrites the given query to remove the subquery, which would normally be great but <strong>is not applicable in this case</strong>. How do I pass a parameter to a subquery in SQL Server?</p>
  
  <p>Column aliases do not work with this bug--Using <code>AS</code> returns an unknown column error on the "duplicate" columns even though it works with all others. The last two lines in the <code>SELECT</code> clause work because the values are being queried so the report can use them, but the remainder of the actual query doesn't use them.</p>
  
  <p>Here's my current code (doesn't work because the subquery returns multiple rows).</p>
  
  <pre><code>SELECT t.[Description],
         t.RequestedCompletionDate,
         t.CommitDate,
         t.StatusId,
         t.PriorityId,
         p.ProjectNumber,
         s.Name AS StatusDescription,
         pr.Name AS PriorityDescription
  FROM ProjectTask t
  inner join Project p
      on p.Id = t.ProjectId
  inner join Project_TaskStatus s
      on s.Id = t.StatusId
  inner join Project_Priority pr
      on pr.Id = t.PriorityId
  WHERE t.Type = 'ET'
  AND t.StatusId NOT IN (4,7)
  AND
  (
      SELECT StatusId FROM Project
      -- WHERE ?
  )
  NOT IN (3, 4)
  ORDER BY t.PriorityId,
           t.CommitDate,
           t.RequestedCompletionDate
  </code></pre>
  
  <p>This is the code with aliases as requested in the comments. It throws an error:</p>
  
  <pre><code>SELECT t.[Description],
         t.RequestedCompletionDate,
         t.CommitDate,
         t.StatusId AS TaskStatusId,
         t.PriorityId,
         p.ProjectNumber,
         p.StatusId AS ProjectStatusId,
         s.Name AS StatusDescription,
         pr.Name AS PriorityDescription
  FROM ProjectTask t
  inner join Project p
      on p.Id = t.ProjectId
  inner join Project_TaskStatus s
      on s.Id = TaskStatusId
  inner join Project_Priority pr
      on pr.Id = t.PriorityId
  WHERE t.Type = 'ET'
  AND TaskStatusId NOT IN (4,7)
  AND ProjectStatusId NOT IN (3,4)
  ORDER BY t.PriorityId,
           t.CommitDate,
           t.RequestedCompletionDate
  
  -- Invalid column name 'TaskStatusId'.
  -- Invalid column name 'TaskStatusId'.
  -- Invalid column name 'TaskStatusId'.
  -- Invalid column name 'ProjectStatusId'.
  -- Invalid column name 'ProjectStatusId'.
  </code></pre>
  
  <p>The ideal code is below, but it throws the error <code>An item with the same key has already been added</code>, which is the error that SSRS/SSDT throws when trying to return multiple columns of the same name.</p>
  
  <pre><code>SELECT t.[Description],
         t.RequestedCompletionDate,
         t.CommitDate,
         t.StatusId,
         t.PriorityId,
         p.ProjectNumber,
         p.StatusId,
         s.Name AS StatusDescription,
         pr.Name AS PriorityDescription
  FROM ProjectTask t
  inner join Project p
      on p.Id = t.ProjectId
  inner join Project_TaskStatus s
      on s.Id = t.StatusId
  inner join Project_Priority pr
      on pr.Id = t.PriorityId
  WHERE t.Type = 'ET'
  AND t.StatusId NOT IN (4,7)
  AND p.StatusId NOT IN (3,4)
  ORDER BY t.PriorityId,
           t.CommitDate,
           t.RequestedCompletionDate
  </code></pre>