return recordset in string form

[thysonj]

this is killing me..i have a feeling i am over thinking it. I am trying to return a recordset in a string value. I will explain further...

This is a representation of my DB

[u]ElementPK,ElementParent[/u]
1,0
2,1
3,2
4,2
5,2
6,3
7,4
8,5

Now, using a udf with recursion I have no problem returning a recordset of children(and grand children, etc...) for a particular element. For example... udfgetchildren(1) returns

1
2
3
4
5
6
7
8

which is great. But I also need to get a string representation of that like 1,2,3,4,5,6,7,8. I could do this code side but i am concerned about the processing time as the db grows.

I attempted a variation on the udfgetchildren called udfgetchildrenlist which would seem to work except one major problem it gives me an error "Error: 'udfgetchildrenlist' is not a recognized builtin function name." since I am using udfgetchildrenlist as a built in. My code is below.. for that udf.

create function udfgetchildrenlist(@ElementID int)
returns varchar(1000)
as
   begin

    DECLARE @ElementIDList varchar(1000)
    SELECT @ElementIDList = CONVERT(varchar(20),@ElementID)

    DECLARE cur_Level CURSOR LOCAL FOR
    SELECT Element_IDPK FROM tblElement
    WHERE Element_parent = @ElementID
        OPEN cur_Level
        FETCH NEXT FROM cur_Level INTO @ElementID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @ElementIDList = udfgetchildrenlist(@ElementID)
            FETCH NEXT FROM cur_Level INTO @ElementID
        END
    CLOSE cur_Level
    DEALLOCATE cur_Level
   return @ElementIDList
   end

Any ideas on how to get a string? I am not married to any ideas right now so any help would be appreciated.

 

[SQLSister]

When you call a nontable valued function in a select statement it must be preceded by the owner. If it is table valued function you don't need the owner but as it could return more than one record, you don;t call it this way. If I have a table function that I know will only return one record, I use it like a table as below:

Select @Elongitude = Elongitude, @Nlatitude = Nlatitude, @Wlongitude =Wlongitude , @Slatitude = Slatitude from CalcLatLonDistance(@latAirport,@lonAirport,@Distance)

There is also some good information on dealing with hierarchial dat inthe following FAQ:
How do I do ___________ with a Hierarchical structure? faq183-5322

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief