If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Recursive Stored Procedure in SQL SERVER

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 2
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 WANT TO CREATE THIS TREE USING BOTH HEADER(MSTHDRML) AND DETAIL(MSTDTLML) TABLES
************************************************** ********

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.
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
A recursive data structure is stored in a single table that has a circular reference to itself. You are making your task more complicated by splitting them into two tables. Try creating a single table like this:

Table MSTML
MLID int 4 (auto-numbered)
ParentMLID int
ConcatString varchar 20
EffectiveDateFrom smalldatetime
EffectiveDateTo datetime
Qty money

Define cascading referential integrity (update and delete) between MLID and ParentMLID.

Top=level records can be identified by the fact that their ParentMLID is null.

Now you can write code that loops through the table to find all the children for any record:

BEGIN
--This will be our initial value, but you could pass it as a parameter.
Declare @TargetMLID int

--Create a table for accumulating MLIDs.
Declare @MLIDFamily table
(MLID int)

--Start by adding the initial value.
Insert into @MLIDFamily (MLID)
Select @TargetMLID

--Now add any children to the list that are not already on the list.
--Repeat until no more results are returned.
While @@Rowcount > 0
Insert into @MLIDFamily
Select MSTML.MLID
From MSTML
Inner join @MLIDFamily MLIDFamily on MSTML.ParentMLID = MSTML.MLID
Where not exists (Select * from @MLIDFamily CurrentMLIDs where CurrentMLIDs.MLID = MSTML.MLID)
END

To enumerate the entire tree, just start by inserting all the MLID records where ParentMLID is null.

You can, of course, accumulate other information in your table variable, or do a final join against MSTML to get it when you are finished.

You can also use a counter variable to keep track of the indent level, and use it in sorting and formatting your results.

This is a very flexible recursive structure, and I have used it on many occasions.

blindman
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 2
Hi ,
Thank you for the mail.But I cannot change the DB structure since the design was done by the client and when i discussed this problem with them, they said they want the db structure exactly the way they have designed.........
Thanks
Vipin.


Quote:
Originally posted by blindman
A recursive data structure is stored in a single table that has a circular reference to itself. You are making your task more complicated by splitting them into two tables. Try creating a single table like this:

Table MSTML
MLID int 4 (auto-numbered)
ParentMLID int
ConcatString varchar 20
EffectiveDateFrom smalldatetime
EffectiveDateTo datetime
Qty money

Define cascading referential integrity (update and delete) between MLID and ParentMLID.

Top=level records can be identified by the fact that their ParentMLID is null.

Now you can write code that loops through the table to find all the children for any record:

BEGIN
--This will be our initial value, but you could pass it as a parameter.
Declare @TargetMLID int

--Create a table for accumulating MLIDs.
Declare @MLIDFamily table
(MLID int)

--Start by adding the initial value.
Insert into @MLIDFamily (MLID)
Select @TargetMLID

--Now add any children to the list that are not already on the list.
--Repeat until no more results are returned.
While @@Rowcount > 0
Insert into @MLIDFamily
Select MSTML.MLID
From MSTML
Inner join @MLIDFamily MLIDFamily on MSTML.ParentMLID = MSTML.MLID
Where not exists (Select * from @MLIDFamily CurrentMLIDs where CurrentMLIDs.MLID = MSTML.MLID)
END

To enumerate the entire tree, just start by inserting all the MLID records where ParentMLID is null.

You can, of course, accumulate other information in your table variable, or do a final join against MSTML to get it when you are finished.

You can also use a counter variable to keep track of the indent level, and use it in sorting and formatting your results.

This is a very flexible recursive structure, and I have used it on many occasions.

blindman
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: outside the rim
Posts: 999
that's brilliant! I was trying to see how I could iterate through the rows of a table variable while adding new records to the bottom of the table (thereby catching all of the "children's children") using recursion or something similar. But this is a super elegant way of doing it in one step.

I have the benefit of a field called "Position" which get populated when the original BOM is created, so all I have to do is sort by that field once the BOM is reconstructed.
__________________
have fun,
tc

Small, custom, unique programs
email
_________________________________________________
Favorite message from Windows:
There was an error displaying the previous error message

Sadly, there was no error number to look up ...
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
Quote:
Originally Posted by tcace View Post
that's brilliant!
Thanks, but it's also obsolete. You should be using Common Table Expressions (CTEs) to do recursion in later versions of SQL Server.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: outside the rim
Posts: 999
Quote:
You should be using Common Table Expressions (CTEs) to do recursion in later versions of SQL Server.
ok, I do not see how to do this using a CTE (in a view, for example). Here's what I have (I actually placed it in a TVF hoping I could use it in a view):

Code:
CREATE FUNCTION [dbo].[tvf_BOM] (@ParentSpecID As BigInt)
RETURNS @BOMTable TABLE (TableID BigInt, RootItemID BigInt, Position Int, ChildID BigInt)

AS
BEGIN
	-- Set the Parent Item
	INSERT INTO @BOMTable SELECT 0 As TableID, ItemSpecID As RootItemID, Position, ItemSpecID As ChildItemSpecID FROM dbo.ItemSpecStruc WHERE ItemSpecID = @ParentSpecID
	
	-- Iterate through each new item to find matching children
	While @@Rowcount > 0
	BEGIN
		INSERT INTO @BOMTable 
		SELECT Parent.ItemSpecStrucID As TableID, Parent.ItemSpecID As RootItemID, (Parent.Position + 1) As Position, Parent.ChildItemSpecID 
		FROM dbo.ItemSpecStruc Parent Inner Join @BOMTable TempTable on TempTable.ChildID = Parent.ItemSpecID
		Where not exists (SELECT ItemSpecStrucID FROM @BOMTable CurrentIDs WHERE CurrentIDs.TableID = Parent.ItemSpecStrucID)
	END 	

	RETURN
END
__________________
have fun,
tc

Small, custom, unique programs
email
_________________________________________________
Favorite message from Windows:
There was an error displaying the previous error message

Sadly, there was no error number to look up ...
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
Yes, that can be done as a CTE. Look up some examples online. You can put the CTE in a view, or a sproc, or a UDF.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: outside the rim
Posts: 999
hmmm. I did lookup quite a bit, but found I could not use BEGIN and END constructs in the CTE definition. Instead, I used the tvf to get the result set of the all the matching item ID's and then bound it in a view so that I could then use it elsewhere.

If I were working with the data through front end code, I would stick to the function or the sproc, but my destination in this case is crystal, and the limitations of the front end require I have the results in a view or table.
Reply With Quote
  #9 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
You don't need to use BEGIN and END "within" a CTE definition.
But you can use them "around" a CTE definition.
I'll state again that there is no reason this logic could not be placed in a view based upon a CTE.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On