Recursive Store Procedure with declare Cursor - ca

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)

Recursive Store Procedure with declare Cursor - ca

Author

Topic

yhchan2005
Starting Member

26 Posts

Posted - 2007-11-11 : 22:09:49

Hi, i got below sp which will calling back itself ( recursive ) in order to generate the hierarchy. Inside sp, i need to use cursor to hold the
record for looping. The problem is when i execute this sp, SQL server will raise error telling me the cursor already exists or open. Because this
is a recursive sp, how can i declare the cursor to avoid error ? or any other way to do it ? Please Help.

ALTER proc [dbo].[cpGetHierarchyDetail] (@vcPath varchar(1000),
@vcCompanyCode varchar(10),
@vcInvNo varchar(50))

as

Declare @vcPath varchar(1000)
Declare @vcSourceNo varchar(50)
Declare @vcSourceCompanyCode varchar(10)
Declare @vcType varchar(1)
Declare @vcImpExpTrn varchar(1)

Set @vcPath = @vcPath
if @vcInvNo = '' return

-- Declare Cursor to hold the Source Record
-- In some cases, it might contain more than 1 record

Declare csSource Cursor FOR Select InvoiceNo, CompanyCode, Type, ImpExpTrn,
SourceNo, SourceCompanyCode
from hoCombineSource
Where CompanyCode = @vcCompanyCode
and InvoiceNo = @vcInvNo

Open csSource
Fetch Next From csSource Into @vcInvNo, @vcCompanyCode, @vcType, @vcImpExpTrn,
@vcSourceNo, @vcSourceCompanyCode
While @@Fetch_Status = 0 begin

Set @vcPath = @vcPath + '\' + @vcInvNo

Insert into #tTemp(Path, CompanyCode, Type, ImpExpTrn)
Values(@vcPath, @vcCompanyCode, @vcType, @vcImpExpTrn)

-- Call back the same SP to continue
exec dbo.cpGetHierarchyDetail @vcPath, @vcSourceCompanyCode, @vcSourceNo

Fetch Next From csSource Into @vcInvNo, @vcCompanyCode, @vcType, @vcImpExpTrn,
@vcSourceNo, @vcSourceCompanyCode
end
Close csSource
Deallocate csSource

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 02:36:13

To generate the hierarchy, you can make use of CTE. Read about it in sql server help file

Madhivanan

Failing to plan is Planning to fail

yhchan2005
Starting Member

26 Posts

Posted - 2007-11-12 : 04:10:29

Thanks for your advise. Other than CTE, any other way to do it. I just check in SQL2000, CTE is not available.
Reason why i ask this is because some of my vendor application is running in SQL2000 and some of them is SQL2005.

i just try my best to find a solution which can use in both version of SQL. Please help.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 04:40:03

CTE would work in SQL Server 2005
If you use SQL Server 2000, then read about Expanding Hierarchies in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources