Using SQL 2000 I have the following UDF that returns a table of hierarchical
info from an adjacency list. Is is possible to add an identity column to
@.retFindComponents or otherwise sort the table in the order of the hierarchy
ie root first, followed by descendents?
Thanks, Tad
CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT * FROM
dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component
_ID,@.Part_Component_Number,@.Part_Com
ponent_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_T
ype
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
ENDI think I found a solution but it took some reworking. I added an identity
column to the table @.retFindComponents and then changed the INSERT INTO
statements to use explicit column names to avoid conflicting with the
identity column. Because the function is applied recursively it appears tha
t
the leaf nodes in the hierarchy are inserted first and the internal nodes
that branch off the root are added last. Therefore I moved the optional
INSERT INTO statement for @.IncludeRoot=1 to the end of the function. The
resulting table returned by the function is now in an order although I have
to sort by descending ID to get a top-to-bottom view of the hierarchy.
Tad
CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
ID int identity(1,1),
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT Part_Assembly_ID, Part_Component_ID, Part_Component_Number,
Part_Component_Type FROM
dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component
_ID,@.Part_Component_Number,@.Part_Com
ponent_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_T
ype
END
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents (Part_Assembly_ID, Part_Component_ID,
Part_Component_Number, Part_Component_Type)
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
"Tadwick" wrote:
> Using SQL 2000 I have the following UDF that returns a table of hierarchic
al
> info from an adjacency list. Is is possible to add an identity column to
> @.retFindComponents or otherwise sort the table in the order of the hierarc
hy
> ie root first, followed by descendents?
> Thanks, Tad
> CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
> int,@.IncludeRoot bit)
> RETURNS @.retFindComponents TABLE(
> Part_Assembly_ID int,
> Part_Component_ID int,
> Part_Component_Number nvarchar(50),
> Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
> )
> AS
> BEGIN
> IF (@.IncludeRoot=1)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
> Part_id = @.root_id
> END
> DECLARE
> @.Part_Component_ID int,
> @.Part_Assembly_ID int,
> @.Part_Component_Number nvarchar(50),
> @.Part_Component_Type nchar(4)
> DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
> SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Typ
e
> FROM Parts_Usage u
> INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
> WHERE Part_Assembly_ID=@.Root_ID
> OPEN RetrieveComponents
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
> @.Part_Component_Type
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT * FROM
> dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
> INSERT INTO @.retFindComponents
> VALUES(@.Part_Assembly_ID,@.Part_Compone
nt_ID,@.Part_Component_Number,@.Part
_Component_Type)
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID,
> @.Part_Component_Number,@.Part_Component_T
ype
> END
> CLOSE RetrieveComponents
> DEALLOCATE RetrieveComponents
> RETURN
> END
>
