Help in Avoiding the Use of a Cursor

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

General SQL Server Forums

Database Design and Application Architecture

Help in Avoiding the Use of a Cursor

Author

Topic

christopher_kennard
Starting Member

4 Posts

Posted - 2008-04-08 : 11:35:13

Here is a simplified example of a problem I am facing.

I have 2 tables: Tasks and Employees.

Tasks:
(Task_ID, Task_Name, Task_Type, Task_Requirement, Employee_ID)
Employees:
Emp_ID, Emp_Name, Emp_Specialty, Emp_Task_Cnt, Max_Task_Cnt

Requirements: Write a MS SQLServer 2000 Storeed Procedure to:
1. Update the Tasks table by assigning the task to an Employee.
2. Incrememnt the employee's Emp_Task_Cnt for each Task assigned.
3. Match the Employee to the Task by matching the Task_Requirement to the Emp_Specialty.
4. Do not exceed the employee's Max_Task_Cnt.

I have a working solution to the requirements, but it involves using cursor logic. For all the obvious reasons, I wanted to avoid using a cursor (or cursor-like looping structure) but could not figure out any other way to avoid processing the Task table one record at a time because of the: "4. Do not allow an Employee's Task_Cnt to exeed the Max_Task_Cnt."

Q: Is there a way to do this without using a cursor and still meet all of the requirements?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:38:27

Post your working solution with cursor, and we can make suggestion how to rewrite code to be set-based.

E 12°55'05.25"
N 56°04'39.16"

christopher_kennard
Starting Member

4 Posts

Posted - 2008-04-08 : 15:24:28

Peso, Of course. Below find everything one could need to create and test the procedure I ref'ed in thread root.
thank you in advance to all who may reply with help
Chris

-- create proc to test requirements
create proc dbo.proc_assign_tasks
as
declare @task_id int, @task_req char(3)

select task_id, task_requirement
into #tasks
from task
where emp_id is null

declare tsk cursor for
select task_id, task_requirement
from #tasks

open tsk

fetch next from tsk into @task_id, @task_req

declare @emp_id int, @result varchar(3)

while @@fetch_status = 0
begin
set @emp_id = null ; set @result = null

select top 1 @emp_id = emp_id
from employee
where emp_specialty = @task_req
and emp_task_cnt < emp_max_task_cnt
order by emp_task_cnt

if @emp_id is not null
begin
update task
set emp_id = @emp_id
where task_id = @task_id

update employee
set emp_task_cnt = emp_task_cnt + 1
where emp_id = @emp_id
and emp_specialty = @task_req

set @result = 'Yes'
end
else
set @result = 'No'

insert assign_task_log (task_id,emp_id, result) values(@task_id, @emp_id, @result)

fetch next from tsk into @task_id, @task_req
end

close tsk
deallocate tsk
go

-- test tables and load test data
create table dbo.task (task_id tinyint, task_requirement char(3), emp_id tinyint null)
go
insert task (task_id,task_requirement) values( 1, 'AAA')
insert task (task_id,task_requirement) values( 2, 'AAA')
insert task (task_id,task_requirement) values( 3, 'AAA')
insert task (task_id,task_requirement) values( 4, 'BBB')
insert task (task_id,task_requirement) values( 5, 'BBB')
insert task (task_id,task_requirement) values( 6, 'BBB')
insert task (task_id,task_requirement) values( 7, 'CCC')
insert task (task_id,task_requirement) values( 8, 'CCC')
insert task (task_id,task_requirement) values( 9, 'CCC')
insert task (task_id,task_requirement) values( 10, 'CCC')
insert task (task_id,task_requirement) values( 11, 'CCC')
go
create table dbo.employee (emp_id tinyint, emp_specialty char(3), emp_task_cnt tinyint, emp_max_task_cnt tinyint)
go
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (1,'AAA',9,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (2,'AAA',10,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (3,'AAA',10,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (4,'AAA',8,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (5,'AAA',8,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (6,'AAA',9,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (7,'BBB',9,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (8,'BBB',8,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (9,'BBB',10,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (10,'CCC',10,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (11,'CCC',7,10)
insert dbo.employee (emp_id, emp_specialty, emp_task_cnt, emp_max_task_cnt) values (12,'CCC',8,10)
go
create table dbo.assign_task_log (task_id tinyint, emp_id tinyint, result varchar(3))
go

-- test proc w/ test data
select * from task
select * from employee
go
exec dbo.proc_assign_tasks
go
select * from task
select * from employee
select * from assign_task_log
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources