Dynamically Changing what table to select from with SQL CASE statement

Question

I'm trying to write a stored procedure and depending on a certain column value, I want to be able to change what table I select from. I'll try to give an example:

SELECT ItemNumber,
       ItemType, 
       Description
FROM

CASE ItemType
WHEN 'A' THEN TableA
ELSE TableB
END

WHERE 

CASE ItemType
WHEN 'A' THEN ItemNumber = @itemNumber
ELSE PartNumber = @itemNumber
END

As you can see, not only am I dynamically changing the table I select from, but since these two tables were made at two different times by two different people, the column names are different as well.

So, my question is: What is the best way to accomplish this, since SQL Server doesn't seem to like my query I have constructed.

If anyone who sees what I'm trying to do can suggest a better way to do this, I'd be all ears :-)

Answer 1

You can not use CASE statement in FROM clause, but you can use the following instead:

SELECT itemnumber, itemtype, description
  FROM tablea
 WHERE itemnumber = @itemnumber AND itemtype = 'A'
UNION ALL
SELECT itemnumber, itemtype, description
  FROM tableb
 WHERE partnumber = @itemnumber AND itemtype <> 'A'

Answer 2

You could try building the dynamic SQL statement as a string, and then calling the sp_executesql stored procedure to execute the string.

See here for more information and examples.