Is there a way to possibly put a case statement in the from clause?

Question

I'm trying to select from different locations depending on a certain aspect. Is it possible to put a CASE statement in the FROM clause?

Here's what I'm trying to do

FROM (Case WHEN @location = 'location A' THEN stockA 
WHEN @location = 'location B' then stockB end) ss

StockA is what I would be pulling it from if I wasn't selecting multiple locations. SS is the alias.

Answer 1

You cannot do this. Here is a sort-of-close method:

select ab.*
from ((select a.*
       from stockA a
       where @location = 'location A'
      ) union all
      (select b.*
       from stockB b
       where @location = 'location B'
      )
     ) ab