CODEWITHSUNDEEP
sql-server
Ashwanth K A
Ashwanth K A

Reputation: 131

How to store the result of an select statement into a variable in sql server stored procedure

I have a condition like this:

IF @aaa = 'high'
set @bbb = select * from table1
else
set @bbb = select * from table2

I am going to use this variable (@bbb) throughout my stored procedure

is this possible to save a table into a variable?

I tried using temporary table but i am not able to assign it twice.

IF @aaa = 'high'
    set @bbb = select * into #temp from table1
    else
    set @bbb = select * into #temp from table2

it shows #temp is already declared.

Upvotes: 0

Views: 4023

Answers (3)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

No, It is not work like that. You can declare a table variable and insert into inside it.

DECLARE @bbbTable TABLE(
    Id int NOT NULL,
    SampleColumn varchar(50) NOT NULL
);
insert into @bbbTable (Id,SampleColumn)
select Id,SampleColumn from table1

If the table1 and table2 are completely different tables, you should declare two different table variable;

DECLARE @bbbTable TABLE(
    Id int NOT NULL,
    SampleColumn varchar(50) NOT NULL
);
DECLARE @aaaTable TABLE(
    Id int NOT NULL,
    SampleColumn varchar(50) NOT NULL
);

IF @aaa = 'high'
    insert into @bbbTable (Id,SampleColumn)
    select Id,SampleColumn from table1
else
    insert into @aaaTable (Id,SampleColumn)
    select Id,SampleColumn from table2

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You can store only 1 Column/Row to a variable.So you can't say *.

Suppose I want to store the value of Column1 from TableA to a variable, I can use this

SELECT @MyVariable = Column1 FROM TableA

But I Can't Say 

SELECT @MyVariable = * FROM TableA

Even if there is only 1 column in the Table TableA.

Also If there is more than 1 record returned by the Select condition, then it will assign the First value to the Variable.

Or What you need is to store the entire Rows, you can Either use a Temporary table or a table variable.

Temporary Table 

SELECT * INTO #Temp FROM TableA

Table Variable

DECLARE @MyVarTable TABLE
(
Column1 VARCHAR(50),
Column2 VARCHAR(50)
)

INSERT INTO @MyVarTable 
(
Column1 ,
Column2
)
SELECT
Column1 ,
Column2
From MyTable

This Temporary Table and Table variable can be accessed in the same way you access the normal table using SELECT/UPDATE/DELETE Queries. Except :

Temporary tables are created for each session and automatically dropped when the session ends or the Query window is Closed

Table Variables exists only when you execute the Query. So before using the table variable in a query you need to declare the same

Upvotes: 0

ShaiEitan
ShaiEitan

Reputation: 171

You cant insert into a variable more than 1 value. you can use Table Variable to reach your answer like this:

DELCARE @TableResult AS TABLE (Column1 INT, Column2 INT)

IF @aaa = 'high'
BEGIN
    INSERT INTO @TableResult (Column1,Column2)
    SELECT Column1FromTable, Column2FromTable
    FROM   table1
END
ELSE
BEGIN
    INSERT INTO @TableResult (Column1,Column2)
    SELECT Column1FromTable, Column2FromTable
    FROM   table2
END

Of course you can declare more than 2 columns.

Upvotes: 0

Related Questions