Doubt about Cursor naming in recursive function

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 2005 Forums

Transact-SQL (2005)

Doubt about Cursor naming in recursive function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-08 : 08:03:31

Maneesh writes "sir i gets the following error message while using it
Message:
*--------------------------------------
a cursor with the name c2 already exits
*---------------------------------------
The following function is designed as recursive function
When it functions in a large scale , the cursor opened in may not be closed , so this appears...

how can i dynamicaly assign the new cursor name for each cursor in sub call

Table structure
---------------
Table name : AccountGMaster
Fields
------
Head numeric(9)
AcGID numeric(9)
GroupName varchar(50)

here the value in head will be value of AcGID from a row
ie , this table i self linking
i would like to get all subgroups under the particular head
coul u plz suggest any other way?
----------------------------------

CREATE FUNCTION dbo.ListAccounts (@AcGID numeric)
RETURNS @tab table(AID numeric(9),AcName varchar(100)) AS
BEGIN
declare @A numeric(9),@B varchar(100),@ID numeric(9),@Name varchar(100)

declare C1 Cursor for select b.AcGID,b.GroupName from AccountGMaster a inner join AccountGMaster b on a.AcGID=b.Head where a.AcGID=@AcGID
open c1
fetch next from c1 into @A,@B
while @@FETCH_STATUS=0
begin
declare c2 cursor for
select * from ListAccounts(@A)
open c2
fetch next from c2 into @ID,@Name
while @@FETCH_STATUS=0
begin
insert into @tab([AID],[AcName]) values(@ID,@Name)
fetch next from c2 into @ID,@Name
end
close c2
fetch next from c1 into @A,@B
end
close c1
declare c3 cursor for
select AID,AcName from AccountMaster where AcGID=@AcGID
open c3
fetch next from c3 into @ID,@Name
while @@FETCH_STATUS=0
begin
insert into @tab([AID],[AcName]) values(@ID,@Name)
fetch next from c3 into @ID,@Name
end
close c3
return
END"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 08:43:43

Why don't you tell us what you are trying to do, and I am sure we will come up with a smarter and faster way to accomplish your task.

Post sample data and your expected output based on your provided sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources