July 1, 2004 at 2:07 pm
I have this big if statement checking for each different type and writing an update statement for each one. I was wondering if there was a better way to do this better than what I am doing. It adds and subtracts different fields based on type.
if @old_type = 0 and (@new_type = 1 or @new_type = 2 or @new_type = 4 or @new_type = 5) begin
Update Employee_Avail_Hours set vac_hours_remain=(vac_hours_remain + @hours),vac_hours_used = (vac_hours_used - @hours),personal_sick_remain=personal_sick_remain - @hours,personal_sick_used=personal_sick_used + @hours FROM Employee_Avail_Hours where employee_id = @emp_id and year_avail = year(@new_day_off)
end
else if @old_type = 0 and @new_type = 6 begin
Update Employee_Avail_Hours set vac_hours_remain=(vac_hours_remain + @hours),vac_hours_used = (vac_hours_used - @hours),death_remain=death_remain - @hours,death_used=death_used + @hours FROM Employee_Avail_Hours where employee_id = @emp_id and year_avail = year(@new_day_off)
end
else if @old_type = 0 and @new_type = 9 begin
Update Employee_Avail_Hours set vac_hours_remain=(vac_hours_remain + @hours),vac_hours_used = (vac_hours_used - @hours),school_remain=school_remain - @hours,school_used=school_used + @hours FROM Employee_Avail_Hours where employee_id = @emp_id and year_avail = year(@new_day_off)
end
else if (@old_type = 1 or @old_type = 2 or @old_type = 4 or @old_type = 5) and @new_type = 0 begin
Update Employee_Avail_Hours set personal_sick_remain=personal_sick_remain + @hours,personal_sick_used=personal_sick_used - @hours,vac_hours_remain=(vac_hours_remain - @hours),vac_hours_used = (vac_hours_used + @hours) FROM Employee_Avail_Hours where employee_id = @emp_id and year_avail = year(@new_day_off)
end
else if (@old_type = 1 or @old_type = 2 or @old_type = 4 or @old_type = 5) and @new_type = 6 begin
Update Employee_Avail_Hours set personal_sick_remain=personal_sick_remain + @hours,personal_sick_used=personal_sick_used - @hours,death_remain=(death_remain - @hours),death_used = (death_used + @hours) FROM Employee_Avail_Hours where employee_id = @emp_id and year_avail = year(@new_day_off)
end
...etc,etc
Any suggestions are welcome,
Matt
July 1, 2004 at 4:55 pm
Try setting the whole row of conditions based on the consolidation of like items. For instance the above could be done like so (as long as I didn't misread something)
if @old_type = 0
begin
Update
dbo.Employee_Avail_Hours
set
vac_hours_remain =(vac_hours_remain + @hours),
vac_hours_used = (vac_hours_used - @hours),
death_remain = death_remain - (CASE WHEN @new_type = 6 THEN @hours ELSE 0 END),
death_used = death_used + (CASE WHEN @new_type = 6 THEN @hours ELSE 0 END)
school_remain = school_remain - (CASE WHEN @new_type = 9 THEN @hours ELSE 0 END),
school_used = school_used + (CASE WHEN @new_type = 9 THEN @hours ELSE 0 END)
personal_sick_remain = personal_sick_remain - (CASE WHEN @new_type IN (1,2,4,5) THEN @hours ELSE 0 END),
personal_sick_used = personal_sick_used + (CASE WHEN @new_type IN (1,2,4,5) THEN @hours ELSE 0 END)
FROM
dbo.Employee_Avail_Hours
where
employee_id = @emp_id and
year_avail = year(@new_day_off)
end
else if @old_type in (1,2,4,5)
begin
Update
dbo.Employee_Avail_Hours
set
personal_sick_remain = personal_sick_remain + @hours,
personal_sick_used = personal_sick_used - @hours,
vac_hours_remain = vac_hours_remain - CASE WHEN @new_type = 0 THEN @hours ELSE 0 END),
vac_hours_used = vac_hours_used + CASE WHEN @new_type = 0 THEN @hours ELSE 0 END),
death_remain = death_remain - CASE WHEN @new_type = 6 THEN @hours ELSE 0 END),
death_used = death_used + CASE WHEN @new_type = 6 THEN @hours ELSE 0 END)
FROM
dbo.Employee_Avail_Hours
where
employee_id = @emp_id and
year_avail = year(@new_day_off)
end
July 2, 2004 at 6:17 am
I'd suggest using a case statement, too, for legibility and clarity which will help ease maintenance.
July 2, 2004 at 8:15 am
My initial reaction is that this is business logic which may be more appropriate at a higher tier.
Is it possible to perform the checks in a middle-tier component and just have the stored procedure perform a straightforward update?
One advantage is it that any changes to the logic may be easier to perform and test there.
July 2, 2004 at 8:42 am
Thanks that is just what i needed, at least now its a lot easier to read and update and learned of a new way of how I could change some of the other stored procs in this server.
As far as what raf said, it wouldn't matter since SQL Server and Asp.net website both reside on the same server so there wouldnt really be any easier one way or the other besides only 2 people will ever really be using this so any performance gains will be slim to none though it will be something to keep in the back of my mind.
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply