Aaron Thomas Aaron Thomas - 2 months ago 8
SQL Question

Dynamically choose table

There are two tables with the same structure (same columns, same column names, etc).

How can I design a query so that a certain table is queried from, depending on a variable?

DECLARE @MYVAR SMALLINT = 0;

DECLARE @TABLENAME VARCHAR(MAX);
SET @TABLENAME = CASE WHEN @MYVAR = 1 THEN 'TABLE1' ELSE 'TABLE2' END

SELECT @TABLENAME

-- HOW TO DYNAMICALLY SELECT TABLE NAME HERE?
SELECT TOP 1 * FROM @TABLENAME


Technet docs hint at maybe using a table alias here, but the examples don't show anything related to this.

Answer

As you mentioned, you need to build query dynamically and execute it

DECLARE @MYVAR SMALLINT = 0;

DECLARE @TABLENAME VARCHAR(MAX);
SET @TABLENAME = CASE WHEN @MYVAR = 1 THEN 'TABLE1' ELSE 'TABLE2' END

declare @sql  = 'SELECT TOP 1 * FROM '+ quotename(@TABLENAME)

Exec (@sql) -- To execute the query that is built dynamically