How to change table name in from clause based on a parameter?

Question

In a select statement, in from clause, table name changes depending on a parameter. I tried:

from
IIf(@prmdataVal ='Dummy', tbl1, tbl2) T1

It's showing an error. How to do this?

Did you already try to use dynamic SQL? You can't change the from part of a query except in dynamic SQL... — Tyron78, Oct 04 '17 at 10:38
Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable). Or (if it's always exactly those two tables) [this one](https://stackoverflow.com/q/7533539/4137916). — Jeroen Mostert, Oct 04 '17 at 10:39
This kind of pattern generally indicates a database modelling issue — Nick.Mc, Oct 04 '17 at 10:43

score 2 · Answer 1 · answered Oct 04 '17 at 10:42

2

Try something like the following:

DECLARE @stmt nvarchar(max);
SELECT @stmt = 'SELECT * FROM ' + CASE WHEN @prmdataVal = 'Dummy' THEN 'tbl1' ELSE 'tbl2' END
EXEC @stmt

answered Oct 04 '17 at 10:42

Tyron78

score 0 · Answer 2 · answered Oct 05 '17 at 11:25

Declaring a variable for table name and then calling it in the from clause of dynamic SQL, worked.

Declare @TblName AS SYSNAME = CASE WHEN @prmdataVal='Dummy' THEN 'tbl1'
ELSE 'tbl2' end

and then calling it in the dynamic sql:

from '+@TblName +' as T1...

2 Answers2