自己写的代码有人能帮改一下吗
存储过程3:工资信息查询
写存储过程查询教职工工资
输入:员工号、发放年月
输出:应发工资是否正确,如正确,Return 1;不正确,Return 状态0,并修改工资信息(含基本工资、课时费、应发工资)。
注:根据输入的年月判断当前的学期,如输入2021-04,则为2021年第一学期(1-6月为当年第1学期,7-12月为当年第二学期),根据学期判断教师带教学班的授课时数来计算工资。工资计算公式如下:
应发工资 = 基本工资 +课时费(提示:每月课时费是学期总课时费平均到每个月)
基本工资:讲师(1000),副教授(1500),教授(2000)
课时费 = 每课时工资 × 课时数 (提示:课时数是教师本学期所教授每门课程课时总和/6个月)
每课时工资:讲师(80),副教授(90),教授(100)
如果是普通职员,则基本工资为3000,课时费为0。
alter proc 工资信息查询
@员工号 char(10),
@发放年月 char(20)
as
begin try
if exists(select * from Wages where wagesdata=@发放年月)
if exists (select * from Employee where EmployeeID=@员工号)
begin
declare @应发工资 int set @应发工资=0
declare @类型 char(10)
declare @职称 char(10)
declare @课时数 float
select @课时数 from Course join Kaike on Course.CourseID=Kaike.CourseID where xueshi/6=@课时数 and EmployeeID=@员工号
if exists( select payable=@应发工资 from Wages join Employee on Wages.EmployeeID=Employee.EmployeeID
join Class on Class.EmployeeID=Employee.EmployeeID where payable=3000 and
leibie=(select leibie=@类型 from Employee join Class on Class.EmployeeID=Employee.EmployeeID
where @员工号= Employee.EmployeeID and leibie ='职员') )
begin
return 1
end
else
return 0
update Wages set Basew=3000
update Wages set hourw=0
update Wages set payable=3000
if exists(select payable=@应发工资 from Wages join Employee on Wages.EmployeeID=Employee.EmployeeID
join Teacher on Teacher.EmployeeID=Employee.EmployeeID where Basew=1000 and
title=(select title=@职称 from Teacher where title='讲师') )
begin
return 1
end
else
return 0
update Wages set Basew=1000
update Wages set hourw=80*@课时数
update Wages set payable=1000+80*@课时数
if exists(select payable=@应发工资 from Wages join Employee on Wages.EmployeeID=Employee.EmployeeID
join Teacher on Teacher.EmployeeID=Employee.EmployeeID where Basew=1500 and
title=(select title=@职称 from Teacher where title='副教授') )
begin
return 1
end
else
return 0
update Wages set Basew=1500
update Wages set hourw=90*@课时数
update Wages set payable=1500+90*@课时数
if exists(select payable=@应发工资 from Wages join Employee on Wages.EmployeeID=Employee.EmployeeID
join Teacher on Teacher.EmployeeID=Employee.EmployeeID where Basew=2000 and
title=(select title=@职称 from Teacher where title='教授') )
begin
return 1
end
else
return 0
update Wages set Basew=2000
update Wages set hourw=100*@课时数
update Wages set payable=2000+100*@课时数
end
end try
begin catch
print'error'
end catch