473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Recursive Stored Procedure?

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
4 12948
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robert Song | last post by:
Hi all I am implementing a stored procedure which needs to recursively call itself until specific condition is reached, Could anyone give some advice about that? Thanks a lot Robert Song
4
by: Rodusa | last post by:
I am having problem to apply updates into this function below. I tried using cursor for updates, etc. but no success. Sql server keeps telling me that I cannot execute insert or update from inside...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
5
by: purushneel | last post by:
Hi, I work primarily on Oracle databases. I am trying to convert a recursive stored procedure written in Oracle to DB2. Does DB2 UDB v8.2 (Windows/AIX) supports recursive stored procedures ??...
3
by: oregondba | last post by:
I have a database I have inherited (new job). I am trying to baseline the code and have done a DB2Look to get the code out of an existing db. The DB2Look produced a SP with the following form: ...
0
by: champ1979 | last post by:
I wrote an algorithm to get all the relatives of a person in a family tree. I'm basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call...
3
by: jzdoh | last post by:
I am trying to write a stored procedure that could create a table called tblManagerHierarchy. It is a table that contains recursive data. The data is coming from tblEmployee where it contains that...
3
by: fabiomoggi | last post by:
Hello Guys, I am developing a web application to manage Active Directory resources, and one of my tasks is to map Organizational Units hierarchy into a SQL Server database. Let's suppose that I...
1
by: bipinskulkarni | last post by:
How to write recursive stored procedure in mysql?
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.