SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What can be used as Arrays in SQL Server?


What can be used as Arrays in SQL Server?

Author
Message
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 798
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.
Kivan
Kivan
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 73
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
Shadab Shah
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 798
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.
Kivan
Kivan
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 73
--------- some logic
--------- 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
--------- some logic
from tablename TN join #TempEmp TE TN.empid=TE.empid

Hope this Works......
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212085 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212085 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 798
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search