October 20, 2008 at 1:59 pm
I wrote a function that I am not happy with the way it turned out. It recursively climbs the hierarchy of my parent/child datatable and returns the results as XML.
The problem is that I couldn't find any way to SET a variable to the results of a SELECT statement that begins with WITH (uses a CTE).
What I would've liked to do is this: (Doesn't work)
CREATE FUNCTION spf_IdHierarchy(@Id INT)
RETURNS XML
AS
BEGIN
declare @ret XML;
SET @ret = (
WITH IdHierarchy (Id, IdLevel)
AS
(
SELECT @Id Id, 1 IdLevel
UNION ALL
SELECT tob.ParentId, IdLevel+1
FROM dbo.TreeObjects tob INNER JOIN IdHierarchy ih ON tob.Id = ih.Id AND ParentId > 0
)
SELECT Id, ROW_NUMBER() OVER (ORDER BY IdLevel DESC) Level
FROM IdHierarchy Id
FOR XML AUTO, ROOT('Ids')
)
RETURN @ret
END
What I was forced to do was this:
ALTER FUNCTION dbo.spf_IdHierarchy ( @Id INT )
RETURNS XML
AS BEGIN
DECLARE @ret XML ;
DECLARE @Ids TABLE ( Id INT, Level INT ) ;
WITH IdHierarchy ( Id, IdLevel )
AS ( SELECT @Id Id,
1 IdLevel
UNION ALL
SELECT tob.ParentId,
IdLevel + 1
FROM dbo.TreeObjects tob
INNER JOIN IdHierarchy ih ON tob.Id = ih.Id
AND ParentId > 0
)
INSERT @Ids ( Id, Level )
SELECT Id,
ROW_NUMBER() OVER ( ORDER BY IdLevel DESC ) Level
FROM IdHierarchy Id
SET @ret = ( SELECT Id,
Level
FROM @Ids Id
FOR
XML AUTO,
ROOT('Ids')
)
RETURN @ret
END
Is there any way I could have done this without a cheesy table variable?
Warm Regards,Greg Wilsonsolidrockstable.com
October 20, 2008 at 2:21 pm
Would this work?
[font="Courier New"]CREATE FUNCTION spf_IdHierarchy(@Id INT)
RETURNS XML
AS
BEGIN
DECLARE @ret XML;
WITH IdHierarchy (Id, IdLevel)
AS
(
SELECT @Id Id, 1 IdLevel
UNION ALL
SELECT tob.ParentId, IdLevel+1
FROM dbo.TreeObjects tob INNER JOIN IdHierarchy ih ON tob.Id = ih.Id AND ParentId > 0
)
SELECT
@ret = (
SELECT
Id,
ROW_NUMBER() OVER (ORDER BY IdLevel DESC) LEVEL
FROM
IdHierarchy Id
FOR XML AUTO, ROOT('Ids')
)
RETURN @ret
END
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply