存储过程优化~解决思路

CREATE Procedure [dbo].[P_add_oa_schearrange_new]
@userid int,
@range varchar(50),
@rangedate datetime,
@rangeweek varchar(50),
@overtime varchar(50),
@rangeid int,
@overtimeid int,
@groupid int,
@remark varchar(100),
@overremark varchar(100)
As
declare @result int 
set @result=0;
declare @deptid varchar(50);
select @deptid=deptid from oa_loginuser where userid=@userid;
declare @titleid int
select @titleid=titleid from oa_loginuser where userid=@userid;
declare @auorder int
select @auorder=auorder from oa_arrangeorder where userid=@userid;
declare @holvalue float;
select @holvalue=holvalue from oa_schedul where schedulid=@rangeid;
declare @overvalue float;
select @overvalue=holvalue from oa_schedul where schedulid=@overtimeid;
if(@auorder=9999)
begin
select @auorder=auorder from oa_loginuser where userid=@userid;
end
if (@groupid=-1)
begin
if (select count(*) from oa_schearrange where userid=@userid and rangedate=@rangedate)=0
begin
	select @groupid=groupno from oa_arrangeorder where userid=@userid;
	if (@overtimeid=-1)
	begin
		insert into oa_schearrange( userid,deptid,rangeid,range,rangedate,rangeweek,degree,groupid,auorder,remark,holvalue,createdate )
		values(@userid,@deptid,@rangeid,@range,@rangedate,@rangeweek,0,@groupid,@auorder,@remark,@holvalue,getdate());
	end
	else if(@rangeid=-1)
	begin
		insert into oa_schearrange( userid,deptid,overtimeid,overtime,rangedate,rangeweek,degree,groupid,auorder,overremark,overvalue,createdate )
		values(@userid,@deptid,@overtimeid,@overtime,@rangedate,@rangeweek,0,@groupid,@auorder,@overremark,@overvalue,getdate());
	end

end
else
begin
	select @groupid=groupno from oa_arrangeorder where userid=@userid;
	if(@overtimeid=-1)
	begin
		update oa_schearrange set userid=@userid,deptid=@deptid,rangeid=@rangeid,range=@range,rangedate=@rangedate,
		rangeweek=@rangeweek,groupid=@groupid,auorder=@auorder,remark=@remark,holvalue=@holvalue,lasteditdate=getdate() where userid=@userid and rangedate=@rangedate;
	end
	else if(@rangeid=-1)
	begin
		update oa_schearrange set userid=@userid,deptid=@deptid,overtimeid=@overtimeid,overtime=@overtime,rangedate=@rangedate,
		rangeweek=@rangeweek,groupid=@groupid,auorder=@auorder,overremark=@overremark,overvalue=@overvalue,lasteditdate=getdate() where userid=@userid and rangedate=@rangedate;
	end
end
end
else
begin 
if (select count(*) from oa_schearrange where userid=@userid and rangedate=@rangedate)=0
begin
	if (@overtimeid=-1)
	begin
		insert into oa_schearrange( userid,deptid,rangeid,range,rangedate,rangeweek,degree,groupid,auorder,remark,holvalue,createdate )
		values(@userid,@deptid,@rangeid,@range,@rangedate,@rangeweek,0,@groupid,@auorder,@remark,@holvalue,getdate());
	end
	else if(@rangeid=-1)
	begin
		insert into oa_schearrange( userid,deptid,overtimeid,overtime,rangedate,rangeweek,degree,groupid,auorder,overremark,overvalue,createdate )
		values(@userid,@deptid,@overtimeid,@overtime,@rangedate,@rangeweek,0,@groupid,@auorder,@overremark,@overvalue,getdate());
	end

end
else if(@overtimeid=-1)
	begin
		update oa_schearrange set userid=@userid,deptid=@deptid,rangeid=@rangeid,range=@range,rangedate=@rangedate,
		rangeweek=@rangeweek,groupid=@groupid,auorder=@auorder,remark=@remark,holvalue=@holvalue,lasteditdate=getdate() where userid=@userid and rangedate=@rangedate;
	end
	else if(@rangeid=-1)
	begin
		update oa_schearrange set userid=@userid,deptid=@deptid,overtimeid=@overtimeid,overtime=@overtime,rangedate=@rangedate,
		rangeweek=@rangeweek,groupid=@groupid,auorder=@auorder,overremark=@overremark,overvalue=@overvalue,lasteditdate=getdate() where userid=@userid and rangedate=@rangedate;
	end
end

 
if(@rangeid=32)
	begin
	exec P_CalcMarriageLeave @userid,@rangeid
	end
if(@rangeid=33)
	begin
	exec P_CalcBirth @userid,@rangeid
	end
if(@overtimeid=33)
	begin
	exec P_CalcBirth @userid,@overtimeid
	end
if(@rangeid=28)
	begin
	exec P_CalcYearHoliday @userid,@rangeid
	end 
if(@overtimeid=28)
	begin
	exec P_CalcYearHoliday @userid,@overtimeid
	end
if(@rangeid=40 or @overtimeid=40)
	begin
	exec P_CalcSickday @userid,@rangeid,@overtimeid,@rangedate
	end
if(@rangeid=62)
	begin
	exec P_CalcFuneral @userid,@rangeid
	end
if(@overtimeid=62)
	begin
	exec P_CalcFuneral @userid,@overtimeid
	end
if(@rangeid=60)
	begin
	exec P_CalcAbsence @userid,@rangeid
	end
if(@overtimeid=60)
	begin
	exec P_CalcAbsence @userid,@overtimeid
	end
if(@rangeid=66)
	begin
	exec P_CalcTravel @userid,@rangeid
	end
if(@overtimeid=66)
	begin
	exec P_CalcTravel @userid,@overtimeid