By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,086 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,086 IT Pros & Developers. It's quick & easy.

Recursive Stored Procedure?

P: n/a
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
Oct 16 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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."
Oct 16 '06 #2

P: n/a
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."

Oct 16 '06 #3

P: n/a
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."
Oct 16 '06 #4

P: n/a
Henrik Juul wrote:
CREATE PROCEDURE dbo.GetParentIONode
Henrik, I did something very similar to what you're trying to do here
and explained it in this post:

http://groups.google.com/group/comp....2f2242e783204d

It is non-recursive and, I think, a bit faster than the recursive
method.

Oct 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.