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 2000 Forums  SQL Server Development (2000)  cursor inside another cursor (looping)

Author  Topic  mnorton Starting Member 7 Posts Posted - 2001-04-03 : 17:35:03 I have a stored procedure that retrieves part numbers then, for each individual part number a build plan is retrieved that contains sub-assy part numbers that make up that original part no. After it retrieves the sub-assy part numbers it then does a calculation and then adds that calculation to the original part numbers lot size and updates the table. Right now I am trying to use a cursor inside another cursor but I am having trouble because I cannot declare the inner cursor and keep opening it after the while fetch_status statement. What I need is a way to be able to loop through the original part numbers and then based on those part numbers, I need to loop through all the sub-assy part numbers to do a calculation on each one. The following is the code that I am using. The code is in the initial stages and any suggestions would be greatly appreciated. Thanks for any help that you can provide. set nocount on --Temporary table to hold finished good lotsizes from lotsize report create table #temp_fg(asm_no varchar (30) not null, part_no varchar(30) not null, location varchar(20) not null, src_loc varchar(20) not null, lot_size decimal(20) not null, pass_status varchar(1) null) insert #temp_fg select part_no, '0', location, src_loc, lot_size, "0" from ATC_fg_lotsize where src_loc <> ' ' --Temporary table to hold the calculated lot_sizes after retrieving the qty from what_part table create table #temp_build(asm_no varchar(30), part_no varchar (30) not null, location varchar(30) not null, src_loc varchar(20) not null, lot_size decimal(20, 8) not null) --Temporary table to hold original lotsize for locations that will be adjusted create table #temp_lotsize(part_no varchar(30) not null, location varchar(30) not null, src_loc varchar(20) not null, lot_size decimal(20, 8) not null) --Insert original lotsizes from ATC_inv_list insert #temp_lotsize select part_no, location, src_loc, lot_size from ATC_inv_list where lot_size > 0 --Cursor that holds the scrolls through the part_nos from the #temp_fg table declare fg_parts cursor for select asm_no, part_no, location, src_loc, lot_size from #temp_fg open fg_parts declare @asm_no varchar(30), @part_no varchar(30), @location varchar(30), @src_loc varchar(30), @lot_size decimal(20) fetch next from fg_parts into @asm_no, @part_no , @location, @src_loc, @lot_size --while @@fetch_status =0 *******Please note this is where I need to put the other fetch status so that it will loop through all the part numbers*********** begin select @asm_no, @part_no, @location, @src_loc, @lot_size --insert into #temp_build(asm_no, part_no, location, src_loc, lot_size) --values(@asm_no, @part_no, @location, @src_loc, @lot_size) end --RESULTS --01100504, 0, BM, GB --etc --Cursor that collects the part breakdown from the what_part table based on the asm_no from the #temp_build table declare build_plan cursor for select asm_no, part_no, location, qty from what_part where asm_no = @asm_no and uom <> 'HR' and (location = @src_loc or location ='ALL') declare @bp_asm_no varchar(30), @bp_part_no varchar(30), @bp_location varchar(10), @bp_qty decimal(20, 8) open build_plan fetch build_plan into @bp_asm_no, @bp_part_no, @bp_location, @bp_qty while @@fetch_status = 0 begin select @bp_asm_no, @bp_part_no, @bp_location, @bp_qty --****RESULTS --01100504, 00021006, ALL, 0.05000000 --01100504, 09900011, ALL, 1.00000000 --etc insert into #temp_build(asm_no, part_no, location, src_loc, lot_size) values(@bp_asm_no, @bp_part_no, @bp_location, @src_loc, (@lot_size * @bp_qty)) select part_no, location, src_loc, lot_size from #temp_lotsize where part_no = @bp_part_no declare @update_lotsize decimal(20,8) select @update_lotsize = lot_size from #temp_build where part_no = @bp_part_no update #temp_lotsize set lot_size = (lot_size + @update_lotsize) where (part_no = @bp_part_no and location = @src_loc) or (part_no = @bp_part_no and src_loc = ' ') fetch next from build_plan into @bp_asm_no, @bp_part_no, @bp_location, @bp_qty end --Code works to here but must figure out a way to have a cursor inside of a fetch_status