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

Question

My query is to assign single varible 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

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) 

Or as per Zoran stated use cursors

Answer 2

Vijay, actually i didn't understand your thread properly. Can you explain with more sample representation and what you want to achieve. First of all, use cursors only when there is no other way around. U can use Joins, cte etc..

But you need explain the problem in detailed.

Answer 3

In your previous thread (http://www.c-sharpcorner.com/Forums/Thread/130059/random-number-generation-in-sql.aspx), we were talking about getting the first @id rows from the Flash_Temp2 table.

I'd therefore assumed that this was what you still wanted to do but using successive values of @id fetched from the cursor.

However, if I'm wrong about this and you still want to pursue a solution using a cursor, can you clarify what you want to do with the @id values, please?

Answer 4

Hi everyone, Thanks for your reply.
Dorababu, i tried your solution it works well, thanks!!!. And i am also looking for Cursor solution that Zoran and vulpes asked to do but as i haven't work work with cursor yet.

Answer 5

I removed that line then also it shows two times as shown in the attached file.

Answer 6

You've got this line at the start of the begin ... end block:

set @id = 2

which is overriding the value of @id fetched from the cursor. So try it with that line removed.

Answer 7

Hi Vulpes,
i tried as you and zoran by using cursor
The example that you have given
it gives result four times. please check it in the attached file. And let me know whats going wrong as i don't have any idea regarding cursor.

Answer 8

Hi Zoran,
Let me try your way as well. As i am not friendly with cursor hence didn't tried that but i like to try as well.

Answer 9

Have you tried with the one i posted last or as per Zoran said use cursors

Answer 10

Dorababu, as you said, i tried below query also, it also works
fine
declare @id table (id int) 

 
insert into @id (id) 
select EmpId from Employee 
 
select * from @id 

but my qustion is now

suppose this query returns me three employee id's say 100, 101, 102
now how i will get this employee id in variable one by one because i want to update some information of particular employee(100, 101 and 102) by using employeeid. please suggest me.

Answer 11

Guys, I don't get this discussion... Why would you not use cursor? It's designed for the task described in the question:

"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." - Well... you create a cursor, then iterate values one by one and perform the calculation you need. That is the point of cursors. There's no need to create temporary tables or do anything like that because all that would only be a bleak simulation of a cursor.

In particular case with IDs that would look something like this:

DECLARE MyCursor CURSOR FOR SELECT EmpId FROM Employee
DECLARE @id int
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @id
WHILE (@@FETCH_STATUS = 0)
        BEGIN
                -- now do whatever needed with @id, e.g.: SELECT @id which only prints out the value
                FETCH NEXT MyCursor INTO @id
        END
CLOSE MyCursor
DEALLOCATE MyCursor
GO

I don't have SQL Server instance at hand right now so I can't try this piece of script, but that is basically it.

Answer 12

Have you tried the later one

Answer 13

Hi Dorababu,
With your first example i get the data it shows one by on by using select query
but how can i will get one value at a time

declare @tmpID table(EmpId int)
insert into @tmpID select EmpId From Employee
select EmpId set TempId2 = Empid(this time it should be 2)from @tmpID

in query result it gives me which is perfect
1
2
3
4
5
i want these empid to be use one by one
set TempId1=Empid  -- (this time should be 1)
set TempId3 = Empid -- (this time it should be 3)
and so on..
Please help!

Answer 14

Did not get you but try as per below

declare @tmpID table(EmpId int)
insert into @tmpID select EmpId From Employee

Or


declare @id table (id int) 

 
insert into @id (id) 
select EmpId from Employee 
 
select * from @id 

Answer 15

Vijay,

You can iterate through the IDs using the cursor and then perform appropriate action for each value of the identity fetched in the loop.

You can refer to MSDN T-SQL article on WHILE statement for details: http://msdn.microsoft.com/en-us/library/ms178642.aspx

You can also refer to the following article for a thorough example how to operate with values fetched using cursor: http://www.jackdonnell.com/articles/SQL_CURSOR.htm

Zoran