SQL table variable

Question

Here is my simple question: I want to declare a table variable with different columns according to the value of a predefined variable

The query is as below:

if (@a = 1)

begin

  declare @table_1 table (
    col1 int,    
    col2 int,
    col3 int     
 )

end

else

begin

   declare @table_1 table (    
     col1 int,
     col3 int    
   )

end

But the error pump out saying "The variable name '@table_1' has already been declared. Variable names must be unique within a query batch or stored procedure"

Is there any way to do it?

Not sure, but I found a related SO question that *might* help with your scenario: [http://stackoverflow.com/questions/688233/tsql-define-temp-table-or-table-variable-without-defining-schema](http://stackoverflow.com/questions/688233/tsql-define-temp-table-or-table-variable-without-defining-schema) — STW, Jan 21 '10 at 16:55

score 2 · Answer 1 · answered Jan 21 '10 at 16:55

Use different variables, or simply don't use all the columns. You can't change the schema of a table variable mid-query.

For info, if you are migrating some #temp_table code, then note that this is also a bad idea here, as it can force recompiles. Plus mixing DDL and DML is never a great idea.

score 0 · Answer 2 · answered Jan 21 '10 at 16:56

0

What are you then doing with the table in the rest of your query? Can you not call it Table1 and Table2 and then use the relevant one?

answered Jan 21 '10 at 16:56

Robin Day

100,552
23
116
167

score 0 · Answer 3 · answered Jan 21 '10 at 17:03

Firstly, Sql Server does not have a SCOPE for the variables like you are looking for. So not, this cannot be done as you wish.

Have a look at Transact-SQL Variables

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

Secondly, you probably should be using SELECT * to insert into these tables as I think you might be trying.

What you can do is to declare the Table once, before the IF, and only insert the values you wish to use.

Such as

INSERT INTO @table_1 (col1, col2, col3)
SELECT Col1, col2, col3
FROM ...

or

INSERT INTO @table_1 (col1, col3)
SELECT Col1, col3
FROM ...

For your attempt you would however have to declare the Table Variable once, containing at least ALL the columns required, and only insert into the table the columns required, at various steps/conditions. Then return only what you wish to.

SQL table variable

3 Answers3