Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

  3. #3
    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.


    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

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    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 ...

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    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 ...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •