How do I change table used in from clause of a select based on udf parameter?

I have two tables A and B (with exactly the same columns and overall structure) and I'm trying to create a TVF with a select inside it that chooses to use A or B based on a parameter passed to the TVF:

create function dbo.testing(@switch varchar(1))
returns @SomeData table (data int)
  begin
    insert into @SomeData
    select
      number
    from
      [[either A or B depending on the value of @switch]] <===== ???
  return
 end;

The arrow coming in from the right indicates the line I"m not sure how to handle.

What's the right way to do this? I know I can't just slap a string in there so what's the best way to proceed?

You don't need a multi statement TVF here.

You can use an inline one

CREATE FUNCTION dbo.testing(@switch VARCHAR(1))
RETURNS TABLE
AS
    RETURN
      (SELECT number
       FROM   A
       WHERE  @switch = 'A'
       UNION ALL
       SELECT number
       FROM   B
       WHERE  @switch = 'B')

If the value passed for @switch is a variable or parameter (rather than a constant) you may find you get better plans if you use OPTION (RECOMPILE) with this. Particularly if the cardinality of A and B is very different (likely even without the hint only the relevant one will be accessed as the table access will be under a filter with a startup expression predicate).

StackExchange Database Administrators Q#129198, answer score: 5