Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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  
 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