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: 2 days ago @ 11:40 PM
Points: 248, Visits: 569
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: 2 days ago @ 11:40 PM
Points: 248, Visits: 569
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:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 1,945, Visits: 2,860
Before you can drink new teat, you msut empty the old tea from your cup.

SQL programmers would make a VIEW or derived table from this query. That way it will be current whenever it is used. Your mindset is still back in scratch tapes and decks of punch cards where you physically MATERIALIZE the data. Empty the tea from your cup.

Also the table you used should be named Personnel. A table models a completed SET as a high level abstract, not a deck of separate punch cards.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1337657
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: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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: 2 days ago @ 11:40 PM
Points: 248, Visits: 569
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
Posted Tuesday, July 31, 2012 1:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 1,945, Visits: 2,860
Jeff Moden (7/30/2012)
Not all personnel are employee's, Joe. Nor is it always wise to combine the two.


I am pretty sure that he has more than one of them . I also use "Staff", "Personnel", "Labor" and a few other legal terms. One of my favorite DW stories is about Delta Airlines trying to define "Passengers" ; ever think about seeing eye dogs, babies-in-arms, and YoYo Ma's Cello (travels in a 1st class sets)?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1338148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse