kinda recursive sub proc in proc

[nadskram]

i havent used stored procs in a while

i have a temp table to give me my results and i am able to return 1 set of data
but what i want to happen is to traverse up my data tree

lets say you have locations like country, state, county, city, area
where states have a parent of the country, counties have parents of a state, cities in the county on down

when i choose a city, i want it to continue up until theres no more up
so if i pick a city, i would get a recordset of a city, county, state, country

i have the recordset and i want to have a sub-proc INSIDE my stored procedure that goes out looking for the parent, and continues to go up, recursively, until no more parents

so, how do you put a sub-proc inside a proc

thanks

 

[nadskram]

so i am currently suffering form
'make-things-hard-for-me-itis'
to which there is no cure

i could just loop and fill the recordset

 

[zhavic]

Look there, may be what you are looking for:

http://support.microsoft.com/default.aspx?scid=kb;en-us;248915

And also this, but only if your tree has less than 32 ( better say 20 ) nested levels:
thread183-632899

PS: how do you put a sub-proc inside a proc
You can simply call the same procedure from itself to create recursion, but on SQL server there can be only 32 nested levels, so it may not working, if you tree as more nested levels. ( see this thread183-632899 )


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.