'Recursive Stored Procedure in SQL SERVER
Hi,
I have 2 SQL SERVER tables MSTHDRML (Header table) & MSTDTLML(details Table)
MSTHDRML
MLID int 4
MLITemID int 4
ConcatString varchar 20
EffectiveDateFrom smalldatetime
EffectiveDateTo datetime
MSTDTLML
MLID int 4 0
ItemID int 4 0
ConcatString varchar 20 1
Qty money 8 1
The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.)
Some Sample Data
MSTHDRML
--------------
MLID MLITemID ConcatString EffectiveDateFrom EffectiveDateTo
1 1000 56V 01/06/2003 31/12/9999
2 1003 Red 01/08/2003 31/12/9999
3 1001 01/08/2003 31/12/9999
4 1007 01/08/2003 31/12/9999
5 1008 01/08/2003 31/12/9999
6 1002 01/08/2003 31/12/9999
7 1005 01/08/2003 31/12/9999
8 2000 01/08/2003 31/12/9999
MSTDTLML
--------------
MLID ItemID ConcatString Qty
1 1001 Round 10
1 1002 Square 20
2 1004 Blue 19
1 1005 Green 22
3 1007 Flat 223
4 1008 100
5 1009 200
6 1010 11
7 1011 22
7 1010 45
7 1012 454
8 2001 5
Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following
1000
|
--- 1001
| |
| --1007
| |_ 1008
----1002 |__1009
| |_1010
|
----1005
************************************************** ***************************
I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML)
************************************************** ***************************
How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
