I am trying to create a stored procedure that uses the output of one select statement as the input for another. I need to nest these 3 or 4 levels deep, but I cant find anything about this in the manuals.
For example, I want to select a country, then find all the regions within that country then the cities within each region.
I have some specific code to execute at each level to create index pages, so I want to be able to do this as nested loops of some type (foreach, 'while not eof'?) if possible.
The reason that I do not want to use joins here is that i will be creating output to different files at each level.
I will be creating a 'country page' that will link to each city region page and a region page for each country that will link to all the cities within the region.
I need to grab each of the values to create the various strings for each of the outfiles.
I am creating HTML pages. I have created views that output the proper html code, and it works one page at a time. What I am trying to do is create the procedure to update these pages once a month.
(originally posted in wrong category)
SELECT cc1.Fips_10_4 as cntry FROM cc1;
#for each country get region codes
SELECT rgn FROM camwme.rgn_center where Fips_10_4 =cntry;
SELECT city from masterlist where region=rng and Fips_10_4 =cntry;