|
|
Posted by Bill on 10/16/06 18:26
Henrik,
I just answered a similar question and have copied the text to this
chain. See if it applies to answer your question.
--DDL and inserts to test:
CREATE TABLE [dbo].[tblCategory](
[categoryid] [int] NOT NULL,
[parentid] [int] NOT NULL,
[description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
)
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
1, 0, 'Computers')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
4, 1, 'PC Components')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
5, 1, 'Storage')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
6, 1, 'Laptops')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
7, 1, 'Desktops')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
8, 1, 'Components')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
9, 4, 'CPUs')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
10, 4, 'Sound Cards')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
11, 4, 'Video Cards')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
12, 9, 'AMD')
INSERT INTO tblCategory ( categoryid, parentid, description ) VALUES (
13, 9, 'Intel')
--Procedure to recursively get all subnodes
Create PROCEDURE getTblCategory
AS
DECLARE @tbls_to_join int
DECLARE @selectCmd varchar(1000)
DECLARE @tblCmd varchar(4000)
DECLARE @cmd varchar(8000)
DECLARE @loop_count int
SET @loop_count = 1
SET @selectCmd = 'SELECT t1.categoryid, t1.parentid, t1.description'
SET @tblCmd = 'FROM tblCategory t1 '
SELECT @tbls_to_join = count(distinct parentid) FROM tblCategory
WHILE @loop_count < @tbls_to_join
BEGIN
SET @loop_count = @loop_count + 1
SET @tblCmd = @tblCmd + char(10) + 'LEFT OUTER JOIN tblCategory t' +
convert(varchar,@loop_count) + char(10)
+ 'ON t' + convert(varchar,@loop_count - 1) + '.categoryid
= t' + convert(varchar,@loop_count) + '.parentid'
SET @selectCmd = @selectCmd + char(10) + ', t' +
convert(varchar,@loop_count)
+ '.categoryid, t' + convert(varchar,@loop_count)
+ '.parentid, t' + convert(varchar,@loop_count) +
'.description'
END
SELECT @cmd = @selectCmd + char(10) + @tblCmd
PRINT @cmd
EXEC (@cmd)
Henrik Juul wrote:
> Thanx Kristian.
>
> Can you show me how??
>
> Regards
> Henrik
> "Kristian Damm Jensen" <kristiandamm@mail.dk> wrote in message
> news:45336c4f$0$904$edfadb0f@dread12.news.tele.dk...
> > Henrik Juul wrote:
> >> How do I call my Stored Procedure recursively:
> >>
> >> CREATE PROCEDURE dbo.GetParentIONode
> >> (
> >> @IONodeID int,
> >> @FullNodeAddress char(100) OUTPUT
> >> )
> >>
> >> AS
> >> BEGIN
> >> DECLARE @ParentIONodeID int
> >> IF EXISTS (SELECT ParentIONodeID FROM IONodes WHERE IONodeID =
> >> @IONodeID) BEGIN
> >> SET @FullNodeAddress = CAST((SELECT ParentIONodeID FROM IONodes WHERE
> >> IONodeID = @IONodeID) AS VARCHAR) + ' / ' + @FullNodeAddress
> >> --CALL SP Again with @ParentIONodeID and @FullNodeAddress until
> >> ParentIONodeID = NULL
> >> SELECT @FullNodeAddress
> >> END
> >> END
> >> GO
> >
> > Like you would call any other stored procedure. But you can't use the
> > result set from a stored procedure directly in your where clause. You have
> > to store it in a temptable.
> >
> > --
> > Regards,
> > Kristian Damm Jensen
> > "This isn't Jeopardy. Answer below the question."
> >
Navigation:
[Reply to this message]
|