Dynamically Changing what table to select from with SQL CASE statement

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'

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.

I'm not sure why you want to do things in one SQL Statement .. I'm not a SQL Server person, but in an Oracle stored procedure you could write something like this

If itemtype = 'A' 
Then 
 <statement for table A>
Else
 <statement for Table B>
End if

Something like this should work in SQL Server, too .. maybe someone could expand on this?