Cursor? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor?

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-10 : 17:30:33

I hope I can explain this clearly
I have one table with two fields
tblCompany
Parent_ID | Company_ID
123 | 345
123 | 567
345 | 895
345 | 967
895 | 654

I need to do this somehow
SELECT Parent_ID, Company_ID FROM tblCompany
WHERE Parent_ID = 123

The query will return
Parent_ID | Company_ID
123 | 345
123 | 567

Then I need to take the Company_ID and see if they are also Parent_ID's

SELECT Parent_ID, Company_ID
FROM tblCompany
WHERE Parent_ID IN (345, 567)

The Query will Return
Parent_ID | Company_ID
345 | 895
345 | 967

I need to automate this somehow. I have to get the Company_IDs from the result set each time and see if they are Parent_ID's

this can go on and on

I can not create stored procedures

I can create temp tables and cursors only

I hope I explained this clearly.
I appreciate any help. I'm really in a jam

Thank you

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-10 : 20:21:11

So you are saying that chain of parent->child is endless?

i.e. Once you figure out the parent ID it will have childs and those childs can have childs. With no stop?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-10 : 21:35:20

Recursive CTE's would work well for this type of problem. The example in the documentation is almost exactly identical to what you are trying to do: http://msdn.microsoft.com/en-us/library/ms186243.aspx#

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 10:17:46

Yes, once I figure out the parent ID it will have childs and then those childs will also be in the parent_ID column having other children and so on. It however is not endless but it can go on for quite sometime

Thank you

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 10:22:36

Recursive CTE is your friend as pointed out by Sunita then but just be careful as it can have performance impact also :)

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 10:30:09

Ok. Thank you both for your help!!! I'm I may be back with questons about CTE as I've never used this before. Thanks again!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 10:48:01

Beware of circular reference.

Parent_ID | Company_ID
345 | 200
200 | 345

will give you an infinite loop.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290

E 12°55'05.63"
N 56°04'39.26"

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 10:52:58

ok thank you. I don't think that will happen based on the way the table was populated. Thank you for the heads up!!!

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 11:07:06

Urg .. yaa if that does exist it will lock the server access for you. So make sure you have DAC enabled (Dedicated Admin Connection) to kill your connection just in case such circular ref exists.

Good point Mr. Peso :).

Mohit.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 11:19:19

Does this appear to be correct

WITH Parent_CTE AS (
SELECT Entity_ID, Parent_Entity_ID
FROM tblRank
WHERE Parent_Entity_ID = 1736746
UNION ALL
SELECT p.Entity_ID, p.Parent_Entity_ID
FROM tblRank p
INNER JOIN Parent_CTE pcte ON pcte.Entity_ID = p.Parent_entity_ID
)
SELECT *
FROM Parent_CTE

I'm receiving an erro
'Incorrect Syntax near the keyword 'WITH'

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 11:29:36

Very Very sorry to have troubled you.....Apprently I'm connecting to a SQL Server 2000 instance. The CTE (from what I understand) will not work. Is there another way to get the results I'm looking for?
Again...I'm sorry about the confusion

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 12:02:40

I think your only choice then is cursor and this is how I can think of ...

SELECT Parent_ID, Child_ID, (SELECT COUNT(*) FROM tblCompany WHERE PARENT_ID = Child_ID) As ChildCount
INTO #ChildListing
FROM tblCompany

Then build cursor from there using ChildCount to see if you need to drill further down .. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2009-03-11 : 12:47:31

Thank again for all the help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources