Add Identity Column to Table Type UDF

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_I D
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_C omponent_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
I 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 that
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_I D
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_C omponent_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
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 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_I D
> 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_C omponent_ID,0)
> INSERT INTO @.retFindComponents
> VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID,
> @.Part_Component_Number,@.Part_Component_Type
> END
> CLOSE RetrieveComponents
> DEALLOCATE RetrieveComponents
> RETURN
> END
>