Recursive stored procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recursive stored procedures

Author

Topic

russellsoft

9 Posts

Posted - 2005-03-14 : 04:29:27

How a recursive stored procedures works?

russellsoft

9 Posts

Posted - 2005-03-14 : 04:32:46

I have this recursive procedure:

ALTER PROCEDURE dbo.cf_price_detail_recursive_update
@pER_TREE_ID int,
@pPercent float,
@pStartDate datetime,
@pRevision_ID int = null
AS
BEGIN
declare @lPrice_Detail_ID int,
@lER_TREE_ID int,
@lInternationalPMC int,
@lNetDC int,
@lDomesticListPrice money

if (@pRevision_ID is null)
select Top 1 @pRevision_ID = Revision_ID from dbo.Price_Master where ActiveCode = 'A'

select @lDomesticListPrice = DomesticListPrice,
@lInternationalPMC = InternationalPMC,
@lNetDC = NetDC
from dbo.Price_Detail
where ER_TREE_ID = @pER_TREE_ID
and ( (EndDate > @pStartDate) or (EndDate is null) )

if (isnull(@lDomesticListPrice, 0) <> 0 ) BEGIN
update dbo.Price_Detail
set EndDate = @pStartDate
where ER_TREE_ID = @pER_TREE_ID
and Revision_ID = @pRevision_ID
and EndDate is null

select @lDomesticListPrice = ROUND( @lDomesticListPrice * @pPercent / 100, 2)
select @lDomesticListPrice = ROUND((@lDomesticListPrice + 2.49) / 5, 0) * 5
-- print cast(@lDomesticListPrice as varchar(200))

exec dbo.cf_price_detail_insert
@lPrice_Detail_ID output,
@pER_TREE_ID = @pER_TREE_ID,
@pNetDC = @lNetDC,
@pInternationalPMC = @lInternationalPMC,
@pDomesticListPrice = @lDomesticListPrice,
@pStartDate = @pStartDate,
@pRevision_ID = @pRevision_ID
select @lPrice_Detail_ID
END

if not exists (
select ER_TREE_ID
from dbo.cf_show_er_tree tree
where tree.ER_TREE_PARENT_ID = @pER_TREE_ID and type not in (1,2)
) return

-- print 'before declare cursor '
DECLARE tables_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
select ER_TREE_ID
from dbo.cf_show_er_tree tree
where tree.ER_TREE_PARENT_ID = @pER_TREE_ID and type not in (1,2)
-- print 'before open cursor '
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @lER_TREE_ID
WHILE (@@fetch_status > -1)
BEGIN
--print 'before exec SP '
exec dbo.cf_price_detail_recursive_update
@lER_TREE_ID,
@pPercent = @pPercent,
@pStartDate = @pStartDate,
@pRevision_ID = @pRevision_ID
FETCH NEXT FROM tables_cursor INTO @lER_TREE_ID
END
-- print 'before Close SP'
CLOSE tables_cursor
DEALLOCATE tables_cursor
END

It must review the tree and update records.
BUT IT gets caught in an endless loop at last node...

russellsoft

9 Posts

Posted - 2005-03-14 : 04:35:16

Maby the problem is in cursor or @@fetch_status variable, which is one for all calls of the procedure?..

russellsoft

9 Posts

Posted - 2005-03-15 : 05:05:15

Can somebody answer my question?...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources