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
ConcatStringvarchar 20
EffectiveDateFromsmalldatetime
EffectiveDateTodatetime
MSTDTLML
MLID int40
ItemID int40
ConcatStringvarchar201
Qty money81
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
--------------
MLIDMLITemID ConcatStringEffectiveDateFromEffectiveDateTo
11000 56V 01/06/2003 31/12/9999
21003 Red 01/08/2003 31/12/9999
31001 01/08/2003 31/12/9999
41007 01/08/2003 31/12/9999
51008 01/08/2003 31/12/9999
61002 01/08/2003 31/12/9999
71005 01/08/2003 31/12/9999
82000 01/08/2003 31/12/9999
MSTDTLML
--------------
MLIDItemIDConcatStringQty
11001Round 10
11002Square 20
21004Blue 19
11005Green 22
31007Flat 223
41008 100
51009 200
61010 11
71011 22
71010 45
71012 454
82001 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