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.
-
Yes, recursion can be done. Doesn't mean it always a good idea! Before we dive into this, is there a reason not to put it all one table? The standard recursive example is employees, where each employee has both an empid and a mgrid, that can handle any number of layers. Parents (the CEO in employees) dont have a mgr id. The downside to any recursive relationship in SQL is retrieving, you essentially have to figure out the max depth first, then do your joins.
Andy
-
Hi
Thank you for the mail.I cannot change the DB structure, since it was designed by the client.When i discussed this with them, they said they want the DB structure exactly the way they have designed.
Please help me on this.
Thanks
Vipin.
-
Take a look at using DataSets. (If you're using ASP.NET). They have a get children method, that may help you recurse through the tables.
-
This all depends on what you are trying to accomplish. Are you trying to display the data? Or are you trying to Report it...two different things in my opinion.
-
Piggybacking on Andy's response.
You could add a separate table with the tree depth for each parent ID. You should keep this up to date using triggers on the parent and child tables.
I would like to see the solution from Andy. I'm interested in seeing how the depth can help in the join...
-
Hey, I shouldnt actually have to WRITE code! The reason depth matters is so you can do the appropriate number of joins to get a set based result. If you've got 4 layers, it looks like:
select * from emp e1 left join emp e2 on e1.pkey =e2.fkey left join emp e3 ..............
Im sure there is a better way, I'll have to find my sql book.
Andy
-
In other words -- a tree walking ala Oracle syntax is required using the CONNECT BY...
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1168
says 'It can't be done?'...
-
Andy, this was the idea I had too, so no need to write the code
. But I was just hoping you had a very clean and neat way of dealing with this, without resorting to dynamic queries and the like.This question comes up very often on these boards, and I was hoping, someone, someday would come up with the killer solution that would make my life much easier...
-
I think you have to do the work one way or another. Perhaps in the next version of SQL...!
Andy
. But I was just hoping you had a very clean and neat way of dealing with this, without resorting to dynamic queries and the like.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply