SQLTeam.com | Weblogs | Forums

How to Create a procedure with nested loop


#1

i have two tables.
Table1

Pegging SKU QTY RDD Component Avilable Qty Avlbl Dte Material Available Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-16

Table2:

SKU Component Qty Per
Notebook1 Keyboard 1
Notebook1 DisplayPanel 1
Notebook1 PowerCard 1
Notebook1 MotherBoard 1
Notebook1 Ram 2
Notebook1 HDD1 1
Notebook1 MousePad 1
HDD1 1TB Seagate 1
HDD1 Seagate Label 2

OUTPUT

Pegging SKU QTY RDD Component Avilable Qty Avlbl Dte Material Available Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 HDD1 2 6-Feb-17 7-Feb-16
SO1 HDD1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-16
SO1 HDD1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-16

PSEDOCODE:

Loop1 : Table1_Peggging read Sales Order Material(SKU) Per sales Order
Loop2: Read full table2 for above Material. (Based on our example data you will get 7 records)
Loop3: Based on Loop2 Component, read Table2 SKU level table2.

Loop 4: If any data exists in Loop3 then
insert into output table.
Loop 4 End.

Loop3 End
Insert Loop2 data into output table.

Loop2 End
Loop 1 End


#2

Step 1: Think in set operations, using e.g. joins. Don't think in loops (and don't write them either unless there really is no other way)


#3

please post your DDL and DML as follows

create table #simi(Pegging varchar(10), SKU varchar(10), 
                            QTY int, RDD datetime, Component varchar(10), 
                          Avilable int, [Qty Avlbl Dte Material], [Available Date] datetime)

insert into #simi
select 'SO1', 'Notebook1', 2, '6-Feb-17', 'Keyboard', 2, '6-Feb-17', '7-Feb-16' union
select 'SO1', 'Notebook1', 2, '6-Feb-17', 'DisplayPanel', 2, '6-Feb-17', '7-Feb-16'

#4

Please post DDL and DML.