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

SQL Server 2000 Forums

Transact-SQL (2000)

Help in Avoiding the use of a Cursor

Author

Topic

christopher_kennard
Starting Member

4 Posts

Posted - 2008-04-09 : 15:37:53

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?

BELOW IS MY SOLUTION USING A CURSOR

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

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-04-09 : 18:26:34

your requirements are not very clear... hwoever here is my take on this.

you can update the count using the following logic.

1. should not require a cusrosr this is just a simple update or insert....

you can just use basic insert into table ... select values statment to get this doen...

2. the incrementing of counter could be done this way...

insert into table
select
DECLARE @counter int
SET @counter = 0
UPDATE Emp_Task_Cnt
SET @counter = counter = @counter + 1

3, is a basic join between the tables..

4, you can have a where clause in in the insert into selct stament that where the count is less than some value...

christopher_kennard
Starting Member

4 Posts

Posted - 2008-04-09 : 23:43:13

bbasir,
I am not seeing how your suggestion could work. look at waht the code in the proc does. what the proc does with a cursor, I wolud like get some help (understandable help) the can allow me to use a set based approach instead.

Can you flesh out what you are saying a bit more?

"requirements unclear"? I could not have simplified the problem to a more base level. what is unclear to you.
1. assign the tasks to employees.
2. keep track of the running total of the total assignments given to each employee
3. employees cannot be assigned more tasks than thier max task limit.
forget the requirements. Analaze the Proc. It's very straight forward.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources