473,372 Members | 1,041 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,372 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 12949
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?
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...
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...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.