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

Corona

While people usually assume "SQL Server" == "MS SQL Server", it's better if you clarify this in the question and in the tags. I'm changing the tags, revert if I'm wrong. – Silica

sqlserver is the tag for Microsoft SQL Server. There is currently no other product by that name, and it's the only useful tag on SO for it. – Cos

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'

Drayage

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.

Saturnian

I doubt that dynamic SQL is really necessary here unless the schema is even more pathological. – Cos

I agree the SP has really bad design, but I think this is the easiest solution to the proposed problem. – Saturnian

I don't think the problem is specified solidly enough to determine that it can't be solve with basic SQL first. Dynamic SQL should not be considered in general until the obstacle to ordinary SQL indicate it is the correct choice. – Cos

Answer 3

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?

Zymotic

Thats what I would do as well – Preconcert

Answer 4

You really aren't explaining where ItemType is coming from. As suggested UNION might be applicable if you are simply combining two tables.

Here's another possibility which may relate to your problem:

SELECT ItemNumber,
       ItemType, 
       COALESCE(TableA.Description, TableB.Description) AS Description
FROM Items
LEFT JOIN TableA
    ON Items.ItemType = 'A'
    AND TableA.ItemNumber = Items.ItemNumber
LEFT JOIN TableB
    ON Items.ItemType <> 'A'
    AND TableB.ItemNumber = Items.ItemNumber

Cos

Answer 5

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

Silica

Answer 6

It could be a dynamic query or you can proceed with the below approach:

SELECT 
  CASE ItemType
    WHEN 'A' THEN (Select ItemNumber from TableA Where ItemNumber = @itemNumber)
    When 'B' THEN (Select ItemNumber from TableB Where ItemNumber = @itemNumber)
  End as ItemNumber,
  CASE ItemType
    WHEN 'A' THEN (Select ItemType from TableA Where ItemNumber = @itemNumber)
    When 'B' THEN (Select ItemType from TableB Where ItemNumber = @itemNumber)
  End as ItemType,
  CASE ItemType
    WHEN 'A' THEN (Select Description from TableA Where ItemNumber = @itemNumber)
    When 'B' THEN (Select Description from TableB Where ItemNumber = @itemNumber)
  End as Description 

Bromleigh

Answer 7

You can do it like this in SQL Server

DECLARE @t AS VARCHAR(1) = 'B';

BEGIN

IF @t = 'A' 
SELECT * FROM TableA
    
IF @t = 'B' 
SELECT * FROM TableB

END

Raki

Answer 8

You can do it with a variable. For example;

DECLARE @YEAR AS INT
SET @YEAR = 2021

DECLARE @TABLENAME AS NVARCHAR(100)

IF @YEAR < 2021
SET @TABLENAME = 'TABLE A'
ELSE 
SET @TABLENAME = 'TABLE B'

EXEC('SELECT * FROM '+@TABLENAME+'')

Hereditary