| Author |
 Topic  |
|
|
christopher_kennard
Starting Member
USA
4 Posts |
 Posted - 04/08/2008 : 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
Sweden
29138 Posts |
Posted - 04/08/2008 : 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
USA
4 Posts |
Posted - 04/08/2008 : 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
|
|
|
| |
Topic |
|
|
|
Help in Avoiding the Use of a Cursor
