Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What can be used as Arrays in SQL Server? Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 9:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 297, Visits: 759
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.
Post #1337647
Posted Monday, July 30, 2012 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:14 PM
Points: 6, 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.
Post #1337649
Posted Monday, July 30, 2012 10:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 297, Visits: 759
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.
Post #1337652
Posted Monday, July 30, 2012 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:14 PM
Points: 6, 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......
Post #1337656
Posted Monday, July 30, 2012 10:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 38,346, Visits: 35,269
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1337660
Posted Monday, July 30, 2012 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 38,346, Visits: 35,269
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1337662
Posted Monday, July 30, 2012 10:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 297, Visits: 759
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
Post #1337663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse