Recursive Stored Procedure - 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

SQL Server Development (2000)

Recursive Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-30 : 09:19:46

shahin mohaghegh writes "I have a SP that is called recursively.
I use Cursor Inside it, the problem is that the name of the cursor is not changed each time the SP is called and this cause error when the SP is called for the second time.
the Error message as is expected is CURSOR ALREADY EXIST.

the simplest solution to the problem is to find a way to be able to change the name of the cursor each time the SP is called.

Also I've done it with the following Trick:
declare @CursorName varchar(20)
declare @SqlString varchar(4000)
declare @Level int

set @Level = 0
set @CursorName = 'TCURSOR' + @Level
set @SqlString = 'DECLARE ' + @CursorName + ' CURSOR FOR SELECT MasterCode, DetailCode, Qty, MasterName, DetailName, Price from ...'

EXEC sp_executeSQL @SQLString

but i can not do the same for :
FETCH NEXT FROM Tcursor0 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price

Thanks."

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-30 : 09:26:50

Declare the cursor as local, then it won't cause conflicts:

DECLARE cursorname CURSOR LOCAL FOR SELECT ......

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-30 : 13:33:59

Another solution is to do away with the cursor completely. This is one of our favorite pasttimes here at SQLTeam, so if you want us to help out, just post your code and what your goal is...

We have been able to dramatically speed up many a process by eliminating the evil cursor.

shahin_mohaghegh
Starting Member

2 Posts

Posted - 2002-05-01 : 04:04:31

the LOCAL option for the cursor did not help.
here is rthe complete case information.
I'm waiting for the answer, thanx

Tables:
PRD
---
Code int
Name varchar (20)
Price int

PRDREL
------
MasterCode int
DetailCode int
Qty int

Sample Values for Tables:
---------------------------
PRD
---
CODE NAME PRICE
---- ---- -----
0 NULL 0
1 One 1000
2 Two 2000
3 Three 3000
4 Four 4000
5 Five 5000
6 Six 6000
7 Seven 7000

PRDREL
------
MASTERCODE DETAILCODE QTY
---- ---- ----
1 3 2
1 2 1
4 5 2
4 1 2
5 2 2
2 6 1
6 7 1

Views:
------
View1
-----
ALTER VIEW dbo.VIEW1
AS
SELECT dbo.prdRel.*, dbo.prd.Name AS MasterName, prd_1.Name AS DetailName, prd_1.Price
FROM dbo.prd INNER JOIN
dbo.prdRel ON dbo.prd.Code = dbo.prdRel.MasterCode INNER JOIN
dbo.prd prd_1 ON dbo.prdRel.DetailCode = prd_1.Code

calling code:
-------------
declare @sum int
exec TotalPrice 1, 0, @Sum output
select @sum

The TotalPrice SP:
------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [TotalPrice]
(@Code int, @Level tinyint, @SumPrice int output)
AS
declare @cnt int

declare @MasterCode int
declare @DetailCode int
declare @Qty int
declare @MasterName varchar(20)
declare @DetailName varchar(20)
declare @Price int

Declare @SqlString nvarchar(1000)

declare @CursorName nvarchar(20)
declare @CodeName nvarchar(20)

declare @TotalSum int

set @CursorName = 'TCursor' + cast(@Level as nvarchar(2))
Set @CodeName = cast(@Code as nvarchar(20))

set @SqlString = 'DECLARE ' + @CursorName + ' CURSOR FOR SELECT MasterCode, DetailCode, Qty, MasterName, DetailName, Price from View1 where MasterCode = ' + @Codename
set @SqlString = @SqlString + N' open ' + @cursorname
EXEC sp_executeSQL @SQLString

SELECT @cnt=count(*) from View1 where MasterCode = @code
if @cnt=0
set @SumPrice=0
else
begin
set @SumPrice = 0
set @TotalSum = 0
/*--------------------*/
--
-- Because i Could not use EXEC sp_executeSQL method
--
if @CursorName = 'Tcursor0'
FETCH NEXT FROM Tcursor0 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor1'
FETCH NEXT FROM Tcursor1 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor2'
FETCH NEXT FROM Tcursor2 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor3'
FETCH NEXT FROM Tcursor3 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor4'
FETCH NEXT FROM Tcursor4 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
------------------*/
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cnt = count(*) from View1 where MasterCode = @DetailCode
if @cnt=0
begin
set @SumPrice = @SumPrice + @Qty * @Price
end
else
begin
Set @Level = @Level+1
exec TotalPrice @DetailCode, @Level, @SumPrice output
set @SumPrice = @Qty * @SumPrice
set @SumPrice = @SumPrice + @TotalSum
end
if @CursorName = 'Tcursor0'
FETCH NEXT FROM Tcursor0 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor1'
FETCH NEXT FROM Tcursor1 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor2'
FETCH NEXT FROM Tcursor2 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor3'
FETCH NEXT FROM Tcursor3 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
if @CursorName = 'Tcursor4'
FETCH NEXT FROM Tcursor4 INTO @MasterCode, @DetailCode, @Qty, @MasterName, @DetailName, @Price
set @TotalSum = @TotalSum + @SumPrice
END
end
set @SqlString = 'CLOSE ' + @CursorName
set @SqlString = @SqlString + N' DEALLOCATE ' + @CursorName
EXEC sp_executeSQL @SQLString

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Edited by - shahin_mohaghegh on 05/01/2002 04:06:03

Edited by - shahin_mohaghegh on 05/01/2002 04:16:52

shahin_mohaghegh
Starting Member

2 Posts

Posted - 2002-05-04 : 08:30:37

thanks to TIMMY for his guide.
i found the solution for my problem.
here it goes:

Create PROCEDURE TotalPrice( @MasterID int ,@Sum int Output)
AS
declare @ItemID int
declare @Name nvarchar(50)
declare @Price int
declare @Qty int

declare @TEmp int

DECLARE TCursor CURSOR LOCAL FOR
SELECT dbo.PRD.*, dbo.PRDREL.Qty
FROM dbo.PRD INNER JOIN
dbo.PRDREL ON dbo.PRD.ItemID = dbo.PRDREL.DetailID
WHERE (dbo.PRDREL.MasterID = @MAsterid)
open tcursor
FETCH NEXT FROM TCursor INTO @ItemID, @Name, @Price, @Qty
set @Sum = 0
set @TEmp = 0
WHILE @@fetch_status = 0
BEGIN
--select @ItemID, @Name, @Price, @Qty
exec TotalPrice @itemid, @Temp output
set @Sum = @Sum + @Price * @Qty + @Temp
FETCH NEXT FROM TCursor INTO @ItemID, @Name, @Price, @Qty
END

CLOSE TCUrsor
DEALLOCATE TCUrsor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources