August 20, 2007 at 5:54 am
Hello all,
Has anybody found a solution for the following problem:
I have a table with a parent-child relationship (column ParentID, column ChildID)
I have a recursive procedure that prints the list parent-child like a tree.
I need to show this list in a webpage.
Can this be done from a stored procedure?
tia,
Hans
August 20, 2007 at 6:56 am
No. The showing of the data has to be done in your ASP page, by calling the SP.
Or, you can create an HTML page from your SP, but why would you?
N 56°04'39.16"
E 12°55'05.25"
August 20, 2007 at 8:26 am
Search hierarchies in SQL Server, but why bother.
Then get the results you need and you'd need to display the tree list in your ASP page. The SQL query would have nothing to do with the display.
August 20, 2007 at 8:47 am
Yes,
executing the stored procedure from asp and taking the resultset is the way to go.
But is there any easy way to preserve the order in which the stored procedure finds and displays the results? In a recursive procedure I have not found a way to keep a counter that I could use as a value to use for sorting the results.
In summary, my problems boils down to this:
- output from the recursive procedure:
Parent1
Child1
Child2
Child3
Child4
I can have this printed from within the stored procedure, and then the hierarchy is fine.
When I store the results in a temporary table, I can only keep the correct order when I do not define any primary key.
So I have a solution here, but I wonder if there is an easier way to do this.
thanks for the help so far.
Hans
August 20, 2007 at 8:49 am
I saw that the layout of the output list was not preserved; what I mean is:
-Parent1
---Child1
------Child2
---Child3
------Child4
Hans
August 20, 2007 at 10:31 am
If you post your table DDL, maybe we can find a new way to return the records you want.
Now that you have CTE's available, it should be easy.
N 56°04'39.16"
E 12°55'05.25"
August 21, 2007 at 5:12 am
Hello Peter,
Here is the create table statement:
CREATE TABLE [dbo].[tblFamily](
[ChildID] [int] IDENTITY(1,1) NOT NULL
,[ParentID] [int] NOT NULL CONSTRAINT [DF_tblFamily_ParentID] DEFAULT ((0))
,[Name] [varchar](255) NOT NULL
,CONSTRAINT [PK_tblFamiliy] PRIMARY KEY CLUSTERED ([ChildID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
regards,
Hans
August 21, 2007 at 9:01 am
You could produce XML in a stored procedure, and then just use XSLT to make it nice. Of course you need to consider the supporting browsers, whether you want some other content, and what kind of other content you want on the page, but this method would allow some extra flexibility, such as traversing the tree, etc. Just an idea
Regards,
Andras
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply