SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Help in Avoiding the Use of a Cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

christopher_kennard
Starting Member

USA
4 Posts

Posted - 04/08/2008 :  11:35:13  Show Profile  Reply with Quote
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
30265 Posts

Posted - 04/08/2008 :  11:38:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

christopher_kennard
Starting Member

USA
4 Posts

Posted - 04/08/2008 :  15:24:28  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000