cursor issue? - 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)

cursor issue?

Author

Topic

plineham
Starting Member

2 Posts

Posted - 2005-03-22 : 08:06:26

I am trying to populate a second table with employee departments based on the hierarchichal position.

The code only runs for the single staff no and doesn't seem to move through the recordset.

Any help would be great

Paul

declare @empid int
declare @origempid int
declare @level int
declare @reccount int
set @reccount = 0
SET NOCOUNT ON
DECLARE Employee_Cursor CURSOR FOR SELECT staffno from employee where RTStaffNo is not null

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor INTO @EmpID
WHILE @@FETCH_STATUS = 0
BEGIN
set @level = 0
set @origempid = @empid
set @reccount = @reccount+1

WHILE @EmpID is not null
BEGIN
set @level = @level +1

insert into Department select @origempid,@empid,ebranchdesc,ebranchno,@level
from employee
where staffno = @empid
SET @empid = (select RTStaffNo FROM employee where staffno = @empid)
END
FETCH NEXT FROM Employee_Cursor
END

CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

print @reccount
select * from department

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 09:02:35

Well basically you have

WHILE @EmpID is not null
BEGIN
SET @empid = (select RTStaffNo FROM employee where staffno = @empid)
END

At a brief glance I don't see how the value in @EmpID is going to change, and the loop exit

Kristen

plineham
Starting Member

2 Posts

Posted - 2005-03-22 : 09:09:03

An error on my part explaining the fields used.

The RTStaffNo is the ReportsToStaffNo and is therefore their managers staff no. Hence it changes during the loop. I believe I have now solved the problem but thanks for your time in any case.

When I fetched the next record within the loop I did not feth it into the variable. The loop now looks like this and seems to work.

FETCH NEXT FROM Employee_Cursor INTO @EmpID
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM Employee_Cursor INTO @EmpID

Paul
END

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 09:26:13

"When I fetched the next record within the loop I did not feth it into the variable"

That would do it!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources