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.

Dorababu Meka · Answer

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

Suthish Nair · Answer

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.

Vulpes · Answer

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?

Vijay Yadav · Answer

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.

Vijay Yadav · Answer

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

Vulpes · Answer

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.

Vijay Yadav · Answer

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.

Vijay Yadav · Answer

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.

Dorababu Meka · Answer

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

Vijay Yadav · Answer

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.

Zoran Horvat · Answer

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.

Dorababu Meka · Answer

Have you tried the later one

Vijay Yadav · Answer

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!

Dorababu Meka · Answer

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

Zoran Horvat · Answer

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