CODEWITHSUNDEEP
sqlsql-server
demonslayer1
demonslayer1

Reputation: 859

How do I access a column that has been stored in temporary table variable in SQL Server

How do I access a column that has been stored in temporary table variable?

I tried to access it this way:

set @Name = WeekName;

but I get an error "column WeekName is undefined".

DECLARE @ListOWeekDays TABLE 
                       (
                          DayNumber INT,
                          DayAbb VARCHAR(40), 
                          WeekName VARCHAR(40)
                       )
DECLARE @Name VARCHAR(40);

INSERT INTO @ListOWeekDays
VALUES 
(1, 'Mon', 'Monday')  ,
(2, 'Tue', 'Tuesday') ,
(3, 'Wed', 'Wednesday') ,
(4, 'Thu', 'Thursday'),
(5, 'Fri', 'Friday'),
(6, 'Sat', 'Saturday'),
(7, 'Sun', 'Sunday')    

DELETE @ListOWeekDays 
WHERE DayNumber = 1

UPDATE @ListOWeekDays 
SET WeekName = 'Saturday is holiday'  
WHERE DayNumber = 6

SELECT * FROM @ListOWeekDays

SET @Name = WeekName;
PRINT @Name;

Upvotes: 0

Views: 708

Answers (1)

marc_s
marc_s

Reputation: 754268

You need to define a SELECT query to get a value from the temporary table variable - just like with any other table, too:

SELECT @Name = WeekName
FROM @ListOfWeekDays
-- add whatever condition makes sense here
WHERE DayNumber = 5;

Since you're storing the value into a single variable - you must ensure that the select only returns a single row - e.g. by specifying a value of the primary key or some other unique value.

Upvotes: 1

Related Questions