|
|
Posted by Designing Solutions WD on 05/01/07 15:58
The procedure I entered above is incorrect.
here is the correct one.
ALTER Procedure [dbo].[GetPendingSettlementsLotNumbers]
(
@CurrentParentTransactionID int = 0
)
as
/*------------------------------------------------------------
Lists the contents of a table designed to represent a multi-
branch tree. The result set takes the form:
ItemID TreeLevel Label
Tree traversal is done non-recursively (to avoid SQL Server's
limit of 32 nested procedure calls)
------------------------------------------------------------*/
--table to hold the result set: a tree structure arranged by level
declare @IndentedTree table(TransactionID int, ParentTransactionID
int, TreeLevel int, LotNumber int, SettlementPrice decimal(32,9))
--table to track where we are in the tree
--this represents a stack turned upside down (with the most resent
item on
--the bottom)
declare @UnvisitedNodes table(StackID int identity(1,1), TransactionID
int, ParentTransactionID int, TreeLevel int, LotNumber int,
SettlementPrice decimal(32,9))
declare @LastTreeLevel int
set nocount on
--initialize the unvisited nodes list
set @LastTreeLevel = 0
INSERT INTO @UnvisitedNodes (TransactionID, ParentTransactionID,
TreeLevel, LotNumber, SettlementPrice)
--SELECT TransactionID, @LastTreeLevel, Label
--FROM tree
--WHERE ParentTransactionID = @CurrentParentTransactionID
--ORDER BY Label desc
Select TransactionID, ParentTransactionID, @LastTreeLevel,
LotNumber, Settlement
From itTransactionProcess
Where ParentTransactionID = @CurrentParentTransactionID and
Settlement is null
ORDER BY ParentTransactionID desc
--Select * from @UnvisitedNodes
--loop through levels of the tree structure
while ((SELECT count(*) FROM @UnvisitedNodes) <> 0)
begin
--add the top item to the result set
INSERT INTO @IndentedTree
SELECT TransactionID, ParentTransactionID, TreeLevel, LotNumber,
SettlementPrice
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes)
--Select * from @IndentedTree
Delete From @IndentedTree where TransactionID in (Select
ParentTransactionID from itTransactionProcess)
--get the top item's ID
set @CurrentParentTransactionID = (
SELECT TransactionID
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes))
--get the top item's indentation level
set @LastTreeLevel = (
SELECT TreeLevel
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes))
--delete that item from the list
DELETE FROM @UnvisitedNodes WHERE TransactionID =
@CurrentParentTransactionID
--add the children of the current item to the top of the list of
unvisited
--nodes
INSERT INTO @UnvisitedNodes (TransactionID, ParentTransactionID,
TreeLevel, LotNumber)
--SELECT TransactionID, @LastTreeLevel + 1, LotNumber
--FROM tree
--WHERE ParentTransactionID = @CurrentParentTransactionID
--ORDER BY LotNumber desc
Select TransactionID, ParentTransactionID, @LastTreeLevel + 1,
LotNumber
From itTransactionProcess
Where ParentTransactionID = @CurrentParentTransactionID and
Settlement is null
ORDER BY ParentTransactionID desc
end
--return the result set
Select LotId From ItLots where LotID not in
(SELECT LotNumber
FROM @IndentedTree) and LotID in (Select LotNumber from
itTransactionProcess)
Navigation:
[Reply to this message]
|