Cursor - 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

Author

Topic

Liza
Starting Member

11 Posts

Posted - 2004-02-18 : 16:39:52

Hi All,

I am doing a nested cursor in SQL and having tons of problems. I am creating this stored procedure to do a one time upload of the data into our system. The tblClass table contains courses such as: 001WELC, 002YAMA, etc. Basically, the curCourses loads all of the courses from tblCourses, then the first course is grabbed. The curEmployees loads all of the employees that have do not have a null value for that course. The employee is inserted into another table. Loop through the curEmployees, then grab a new course from curCourses and start over again. The problems that I am having is that it never gets past the first course that it grabs and it inserts the wrong number of rows into the table - should be 1942 rows but it inserts 2175. Any help would be greatly appreciated!

create proc exec Education_EmpCourses as

declare @sCourseID char(8)
declare @nPersonID int
declare @nExists smallint
declare @nColumnKey int
declare @dCourseDate datetime

declare curCourses scroll cursor for
select rtrim(course_id)
from tblclass
where left(course_id,3) not in ('412','413','414','415','416')

open curCourses

Fetch first from curCourses
into @sCourseID
while (@@Fetch_Status = 0)
begin

select @nExists = count(*)
from tbleduc
where @sCourseID is not null

if @nExists <> 0
begin
EXEC GETColumnKey @nColumnKey output

declare curEmployees scroll cursor for
select personid
from tbleduc
where @sCourseID is not null and
@sCourseID <> ' '

open curEmployees

fetch first from curEmployees
into @nPersonID
while (@@Fetch_Status = 0)
begin
insert into empclass (person_id, column_key, course_id, course_section)
values (@nPersonID, @nColumnKey, @sCourseID,(left(@sCourseID,3) + '.1'))

fetch next from curEmployees
into @nPersonID
end
close curEmployees
end
Fetch Next from curCourses
into @sCourseID
end

deallocate curEmployees
close curCourses
deallocate curCourses

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-02-18 : 17:10:09

lot of work for what you are trying to do. It appears that what you are trying to do can be done without even using a cursor, and would be much easier to read.

Anyway, "EXEC GETColumnKey @nColumnKey output" What does this SP Return?

Liza
Starting Member

11 Posts

Posted - 2004-02-18 : 17:43:32

The "EXEC GETColumnKey @nColumnKey output" just returns a unique numeric key that is used in the table being inserted into. If you have a solution not using a cursor, I would be very happy to hear it!!!

Thanks!

Liza
Starting Member

11 Posts

Posted - 2004-02-18 : 17:53:50

Also, here is what the tblEduc looks like. It is just a temp table used until the data can be properly migrated into our db.

PersonID Name 001WELC 002YAMA
16 Jan Smith 2003-01-01 NULL

There is a column for each of the courses - it used to be an Access table without any normalization.

Once again, any help would be greatly appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources