How to change the structure of a table if a variable is null

Question

I'm selecting a table from a stored procedure which returns different columns if one of my variables is null vs. when it isn't. How do I declare a table differently based on one of my inputs? The code below communicates what I'm trying to do, but I get an error that I'm declaring the table twice if I try to do it this way.

IF @Input IS NOT NULL BEGIN
DECLARE @tbl AS TABLE (
    Var1 FLOAT,
    Var2 DATETIME,
    VAR3 INt,
    ...
)
END
ELSE
DECLARE @tbl AS TABLE(
    Var1 INT,
    Var2 FLOAT,
    Var3 DATETIME,
    ...
)

This isn't possible. Variable declarations happen as though hoisted to the beginning of the batch not at the point of the `DECLARE` so you can't declare them twice. Probably easiest solution is just to declare two different tables variables. — Martin Smith, May 24 '13 at 16:23
That's what I was thinking, 2 tables, then use a variable with your IF statement to point to the correct table from there on out. — Hart CO, May 24 '13 at 16:30
How about using `select into` to create the table from what is returned by the SP? — τεκ, May 24 '13 at 16:39
@τεκ - That will still not be allowed. `IF 1=1 SELECT 1 AS C INTO #T ELSE SELECT 2 AS C INTO #T END` gives `There is already an object named '#T' in the database.` — Martin Smith, May 24 '13 at 16:41
OK. Here's a related question with some possible answers http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure — τεκ, May 24 '13 at 16:43

score 2 · Accepted Answer · answered May 24 '13 at 16:45

2

Use two different table variables ... @stage , @stage1 .

answered May 24 '13 at 16:45

Mudassir Hasan

28,083
20
99
133

score 2 · Answer 2 · answered May 24 '13 at 16:47

This is the only way I can think of to do what you're after, and it isn't friendly. You need to jump scope to declare the same variable table:

DECLARE @Input VARCHAR(5),@TableFields VARCHAR(1000)
SET @Input = NULL
BEGIN
IF @Input IS NOT NULL 
 SET @TableFields = 'Var1 FLOAT,Var2 DATETIME,VAR3 INt' 
ELSE     
 SET @TableFields = 'Var1 INT,Var2 FLOAT,Var3 DATETIME'
EXEC ('DECLARE @tbl AS TABLE ('+@TableFields+')
      INSERT INTO @tbl VALUES (''1'','''','''')
      SELECT * FROM @tbl

    ') 
END

Easier to use 2 table variables and point to each based on your IF logic, imo.

How to change the structure of a table if a variable is null

2 Answers2