Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Recursive Stored Procedure in SQL SERVER

vipinpcvipinpc Member Posts: 1
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 can 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) 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

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.
Sign In or Register to comment.