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" <kr**********@mail.dkwrote in message
news:45*********************@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."