What can be used as Arrays in SQL Server?

Question

What can be used as Arrays in SQL Server?

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
July 30, 2012 at 9:50 pm

#264403

Hi All,
I am new to SQL Server.Prior to this i had done some programming in C.
Consider the below query,
select empid from Emp where deptid=2.
The output of the above query is
empid
1
3
4
5
10
14
15
18
19
20
now i want to store this values in variable and then used the variable in some future query. Since i cannot used array i would like to know what to used.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kivan SSC Enthusiast Points: 120 More actions · Answer 1

Probably you can assign this resultset to a table variable or a temp table and can make use of the same later when required.

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

That's a valid point. But what if my query is something like this

--------- some logic

where empid=(select empid from #emp where deptid=2)

The objective of the query is to execute the logic for each empid.

Kivan SSC Enthusiast Points: 120 More actions · Answer 3

--------- some logic

where empid=(select empid from #emp where deptid=2)

In this case as i said you can insert it (select empid from #emp where deptid=2) to an temptable so tat this temp table 'll be having the corresponding data only. so where ever required you can join with this temp table and achieve the result.

select empid into #TempEmp from emp where deptid=2

--------- some logic

from tablename TN join #TempEmp TE TN.empid=TE.empid

Hope this Works......

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Not all personnel are employee's, Joe. Nor is it always wise to combine the two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

shahsn11 (7/30/2012)
That's a valid point. But what if my query is something like this
--------- some logic
--------- some logic
where empid=(select empid from #emp where deptid=2)
The objective of the query is to execute the logic for each empid.

You can execute the logic for sets of employees in the department instead of for each empid.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 6

Ok that answers my question. Thank you very .... much 🙂 .

But as i was going through your question one more question pop up in my mind.

This code is not the exact code but little bit like a pseudo code

Insert all the records in Emp2 from Emp where deptid=2

which should look something like this

update Emp2 set empid= empid from Emp where deptid=2