Assign single variable a multiple rows using Select query in stored procedure

Question

My query is to assign single variable a multiple rows using Select query in stored procedure For example: I get 10(say 1 to 10) employee ids from from Employee table declare @id int

select @id =EmpId from Employee

select @id

This will return 10 rows(i.e.Employee id , suppose there are 10 rows in a table) now my question is how i will get this all rows in a single variable and use that employee id one by one to perform some calculation.

Answer 1

You cannot insert 10 records into one variable.
What you can do however is:

declare @id table (id int)

insert into @id (id)
select EmpId from Employee

select * from @id

What we have done here is create a table variable, which inserts 1 through 10 as seperate rows. You can now do whatever you want with the table.

Answer 2

Try this:

 declare @tmp table(EmpId int)

    insert into @tmp
    select EmpId From Employee

update Employee
set IsActive = 1
where EmpID in (
Select EmpId from @tmp)