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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy