Question about get multiple records !

I have table with format :

ID Name ParentID
1 England 0
2 Canada 0
3 Manchester 1
4 Chelsi 1
5 Arsenal 1
6 Canada_1 2
7 Canada_2 2
8 MU_1 3
9 MU_2 3
10 MU_1_1 8
...

Now , I write a function which to get information ,such as :
England -- Manchester --MU_1--MU_1_1(*)
My Function :
FunctionGetInformation(ID as int16) as string
' my code here
End function

My problem : (With ID=10)
When i pass this ID , i have to get string which format :(*)
So , what is " my code here " ?
Thank any suggestions ,any advices ...
And thank you very much.

Could you explain what you are looking for in a bit more detail?

|||

I store data in "tree" form .
Example :
+England--Manchester--Manchester1--Manchester1_1 .....
+A--A1--A1_1--A1_1_1 ....
so on !
And i design above table.
My problem :
When i know of node of tree , i can not identify path from root .
such as , i know Manchester1_1 , how can indentify the string :
+England--Manchester--Manchester1--Manchester1_1 .....
You understand me !!
Thank you very much .

|||

Any one help me ? or my bad explaining !
Thank you very much.

|||

CREATE FUNCTION dbo.fnSplit
(
@.List nvarchar(2000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@.SplitOn,@.List)>0)
BEGIN
INSERT INTO
@.RtnValue
(
value
)
SELECT
LTRIM(RTRIM(SUBSTRING(@.List,1,CHARINDEX(@.SplitOn,@.List)-1)))
SET @.List = SUBSTRING(@.List,CHARINDEX(@.SplitOn,@.List)+LEN(@.SplitOn),LEN(@.List))
END
INSERT INTO
@.RtnValue
(
Value
)
SELECT LTRIM(RTRIM(@.List))
RETURN
END

and then use sp or dynamic sql like this

SELECT {something}
FROM {sometable}

INNER JOIN dbo.fnSplit({coma delimited list of ids},{delimter-in this case comma) A ON [dbo].sometable.your id= A.Value

|||

thanh for your guide !! (very much )
It's perfect if you have some comment or explainning what you writting !!
Thank you .

|||I did not really read what you need, other then the heading "get multiple records". This above allows you to pass in a comma delimited list of IDs and return the records back from the stored procedure.|||

There is probably a better way of doing this, and I'm coding this from memory, so some of it may not be 100% correct, but the idea is sound.

function GetInformation(ID as int16) as string

if id<>0 then

dim conn as new sqlConnection(...)

dim cmd as new sqlCommand("SELECT * FROM MyTable WHERE ID=" & ID.tostring)

conn.open

dim dr as SqlDataReader =cmd.Execute(conn)

dim parentID as string =dr("ParentID")

dim n as string=dr("Name")

dr.close

conn.close

return GetInformation(parentID) & n & "--"

else

return ""

end if

end function

|||Thank all !!
To Motley :
You put connection string in function ! and call it each loop !
Performance is good ?|||

It's not the best thing to do, no. In my projects I would have put the connection someplace else. Depending on the dataset size, I might have read the whole thing either into a dataset or a strongly typed collection, and did all my processing there. Depends on how much you need to process and how much data there is.