Dynamically Changing what table to select from with SQL CASE statement

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 :-)

sql sql-server dynamic-sql

Edit Report

1 Answer

You are better of using UNION query to join the tables first, and then SELECT.

Also, you may consider creating a view for one of the tables, so it pulls only the columns you need while renaming them, then UNION, and then select from the UNION.

Or use a temp table to store the result from each query. Put the creation of the temp table in a CASE (pseudocode, not tested):

CASE @itemType
   WHEN 'A'
      SELECT ACol1 AS Col1, ACol2 AS Col2
      FROM TABLE_A
      INTO #tempTable
      WHERE ItemNumber = @itemNumber
   ELSE
      SELECT BCol1 AS Col1, BCol2 AS Col2
      FROM TABLE_B
      INTO #tempTable
      WHERE PartNumber = @itemNumber
END

SELECT * FROM #tempTable

answered 2009-01-29T17:35:10.950

Dynamically Changing what table to select from with SQL CASE statement

1 Answer

Your Answer