--delete from consultant_salary_status --整理工资环境 --exec CorrectIllegalChar --insert into consultant_salary_status values(1,'整理工资环境完毕') --计算Other GBU销量排名 --exec other_store_exponent_proc --insert into consultant_salary_status values(2,'计算Other GBU销量排名') --计算VS销量排名 --exec vs_store_exponent_proc --insert into consultant_salary_status values(3,'计算VS销量排名') --计算HC销量排名 --exec hc_store_exponent_proc --insert into consultant_salary_status values(4,'计算HC销量排名')
--取得工资参数 select @paysalary=paysalary,@taxpoint=taxpoint,@overhead=overhead,@overhead1=overhead1 from salaryconsts
--循环所有的促销顾问 declare consultant_cur cursor for select consultantno from consultant where leftdate = '' or leftdate is null or ( CONVERT(char(10), DATEADD(month, 1,LeftDate), 111) > @salarydate + '/01' and storecode in (select storecode from store )) order by consultantno
open consultant_cur fetch next from consultant_cur into @consultantno while @@fetch_status=0 begin set @typescode = 0 set @ot=0 set @od=0 set @deduction=0 set @bonus1=0 set @levels=0 set @isgd=0 set @cityclass=0 set @agency=0 set @issign=0 set @income=0 set @bcpay=0 set @welfare=0 set @basesalary=0 set @bonus=0 set @true_salary=0 set @true_bonus=0 set @sumsalary=0 set @income=0 set @sumtax=0 set @taxrate=0 set @deductrate=0 set @tax=0 set @aftertax=0 set @consultant_exponent=0 select @levels=levelcode from consultant where consultantno = @consultantno select @isgd=isgd,@cityclass=cityclass from isgd_view where consultantno=@consultantno select @agency=agency from consultant_accountinfo where consultantno=@consultantno select @issign=issign from issign_view where @salarydate>=signdate and @salarydate<=resigndate and consultantno=@consultantno select @ot=cast(ot as dec),@od=cast(od as dec),@deduction=cast(deduction as dec), @bonus1=cast(bonus as dec),@typescode=typescode from duty where salarydate like @salarydate and consultantno=@consultantno if @ot is null set @ot=0 if @od is null set @od=0 if @deduction is null set @deduction=0 if @bonus1 is null set @bonus1=0 exec cpc_proc @consultantno,@salarydate select @cpctotal=total,@n=n from cpc_total where cpc_date=@salarydate and consultantno=@consultantno
if @typescode = '101' begin set @i=@i+1 select @basesalary=amount,@bonus=bonus from levels where levelcode=@levels set @true_salary=@basesalary*dbo.cpc_percent(@cpctotal) --以下是实得资金代码 select @consultant_exponent=consultant_exponent from consultant_exponent_view where consultantno=@consultantno if @consultant_exponent is null set @true_bonus=0 else set @true_bonus=@bonus*@consultant_exponent
set @od=(@true_salary/26)*@od*3 if @ot>167 set @ot=(@true_salary/167)*(@ot-167)*1.5 else set @ot=-(@true_salary+@true_bonus)/167*(167-@ot) set @sumsalary=@true_salary+@true_bonus+@od+@ot+@bonus1-@deduction
--最低保障 if @cityclass like 'A' if @true_salary+@true_bonus<400 begin set @od=(400/26)*@od*3 if @ot>167 set @ot=(400/167)*(@ot-167)*1.5 else set @ot=-(400/167)*(167-@ot) set @sumsalary=400+@od+@ot+@bonus1-@deduction end else if @true_salary+@true_bonus<300 begin set @od=(300/26)*@od*3 if @ot>167 set @ot=(300/167)*(@ot-167)*1.5 else set @ot=-(300/167)*(167-@ot) set @sumsalary=300+@od+@ot+@bonus1-@deduction
end end else if @typescode = '102' begin set @j=@j+1 select @basesalary=amount1,@bonus=bonus1 from levels where levelcode=@levels set @true_salary=@basesalary set @true_bonus=@bonus*dbo.bonus_modulus(@cpctotal) set @od=(@basesalary/26)*@od*3 if @ot>167 set @ot=(@true_salary/167)*(@ot-167)*1.5 else set @ot=-(@true_salary+@true_bonus)/167*(167-@ot) set @sumsalary=@true_salary+@true_bonus+@od+@ot+@bonus1-@deduction
--最低保障 if @cityclass like 'A' if @true_salary+@true_bonus<400 begin set @od=(400/26)*@od*3 if @ot>167 set @ot=(400/167)*(@ot-167)*1.5 else set @ot=-(400/167)*(167-@ot) set @sumsalary=400+@od+@ot+@bonus1-@deduction end else if @true_salary+@true_bonus<300 begin set @od=(300/26)*@od*3 if @ot>167 set @ot=(300/167)*(@ot-167)*1.5 else set @ot=-(300/167)*(167-@ot) set @sumsalary=300+@od+@ot+@bonus1-@deduction end end else if @typescode = '103' begin set @k=@k+1 select @basesalary=amount2 from levels where levelcode=@levels set @true_salary=@basesalary*@ot*dbo.bonus_modulus(@cpctotal) set @true_bonus=0 set @sumsalary=@true_salary+@true_bonus end else begin set @l=@l+1 set @sumsalary=0 set @true_salary=0 set @true_bonus=0 end if @issign='Y' begin select @comlostrate=comlostrate,@comhouserate=comhouserate, @peroldrate=peroldrate,@perlostrate=perlostrate,@perhouserate=perhouserate from salaryconsts set @bcpay=@paysalary*(@peroldrate+@perlostrate+@perhouserate) if @isgd='Y' select @comoldrate=comoldRate,@combabyrate=combabyrate from salaryconsts else select @comoldrate=comoldRate1,@combabyrate=combabyrate1 from salaryconsts set @welfare=@paysalary*(@comoldrate+@comlostrate+@comhouserate+@combabyrate) end else begin set @bcpay=0 set @welfare=0 set @peroldrate=0 set @perlostrate=0 set @perhouserate=0 end set @income=@sumsalary - @bcpay if @income<=@taxpoint set @sumtax=0 else set @sumtax=@income - @taxpoint set @taxrate=dbo.taxrate(@sumtax) set @deductrate=dbo.deductrate(@sumtax) if @sumtax=0 set @tax=0 else set @tax=@sumtax * @taxrate - @deductrate set @aftertax=@income - @tax if @agency='达生' set @managecost=@overhead else set @managecost=@overhead1
insert into consultant_salary1 (consultantno1,salarydate,basesalary,bonus,commision,ot,od, deduction,sumsalary,old,lost,house,bcpay,income,sumtax,taxrate,deductrate,tax, aftertax,welfare,managecost,paysalary,cpc_count) values(@consultantno,@salarydate,@true_salary,@true_bonus,@bonus1,@ot,@od,@deduction, @sumsalary,@peroldrate,@perlostrate,@perhouserate,@bcpay,@income,@sumtax, @taxrate,@deductrate,@tax,@aftertax,@welfare,@managecost,@paysalary,@n)
fetch next from consultant_cur into @consultantno end close consultant_cur deallocate consultant_cur --select @i as 'i',@j as 'j',@k as 'k',@l as 'l' end GO没办法,程序只能写成这样,谢谢各位指点改进!!!!
set @bl=0 加在 select @bl=col from tab where col2='12345'之前 当然也可以在select时加判断 不过我这个人比较懒 能省就省了
而在于你的where 字句
如果select @bl=col from tab where col2='12345'
没有select出记录,那末@bl等于上次的值。
我一般解决方法是:
set @bl=0
从而避免错误运算。
as
begin
--变量定义
declare @consultantno char(5)
declare @consultantno1 char(8)
declare @paysalary dec(9,2)
declare @taxpoint dec(9,2)
declare @overhead dec(9,2)
declare @overhead1 dec(9,2)
declare @comoldrate dec(9,2)
declare @comlostrate dec(9,2)
declare @comhouserate dec(9,2)
declare @combabyrate dec(9,2)
declare @peroldrate dec(9,2)
declare @perlostrate dec(9,2)
declare @perhouserate dec(9,2)
declare @basesalary dec(9,2)
declare @bonus dec(9,2)
declare @cpctotal dec(9,2)
declare @sumsalary dec(9,2)
declare @income dec(9,2)
declare @true_salary dec(9,2)
declare @true_bonus dec(9,2)
declare @ot dec(9,2)
declare @od dec(9,2)
declare @deduction dec(9,2)
declare @bonus1 dec(9,2)
declare @levels char(5)
declare @typescode char(3)
declare @cityclass char(1)
declare @agency char(8)
declare @isgd char(1)
declare @issign char(1)
declare @bcpay dec(9,2)
declare @sumtax dec(9,2)
declare @taxrate dec(9,2)
declare @deductrate dec(9,2)
declare @tax dec(9,2)
declare @aftertax dec(9,2)
declare @welfare dec(9,2)
declare @consultant_exponent dec(9,2)
declare @n int
declare @managecost dec(9,2)
declare @j int
declare @k int
declare @l int
declare @i int
set @k=0
set @l=0
set @j=0
set @i=0
--delete from consultant_salary_status
--整理工资环境
--exec CorrectIllegalChar
--insert into consultant_salary_status values(1,'整理工资环境完毕')
--计算Other GBU销量排名
--exec other_store_exponent_proc
--insert into consultant_salary_status values(2,'计算Other GBU销量排名')
--计算VS销量排名
--exec vs_store_exponent_proc
--insert into consultant_salary_status values(3,'计算VS销量排名')
--计算HC销量排名
--exec hc_store_exponent_proc
--insert into consultant_salary_status values(4,'计算HC销量排名')
--取得工资参数
select @paysalary=paysalary,@taxpoint=taxpoint,@overhead=overhead,@overhead1=overhead1
from salaryconsts
--循环所有的促销顾问
declare consultant_cur cursor for select consultantno from consultant where leftdate = '' or leftdate is null
or ( CONVERT(char(10), DATEADD(month, 1,LeftDate), 111) > @salarydate + '/01'
and storecode in (select storecode from store )) order by consultantno
open consultant_cur
fetch next from consultant_cur into @consultantno
while @@fetch_status=0
begin
set @typescode = 0
set @ot=0
set @od=0
set @deduction=0
set @bonus1=0
set @levels=0
set @isgd=0
set @cityclass=0
set @agency=0
set @issign=0
set @income=0
set @bcpay=0
set @welfare=0
set @basesalary=0
set @bonus=0
set @true_salary=0
set @true_bonus=0
set @sumsalary=0
set @income=0
set @sumtax=0
set @taxrate=0
set @deductrate=0
set @tax=0
set @aftertax=0
set @consultant_exponent=0 select @levels=levelcode from consultant where consultantno = @consultantno
select @isgd=isgd,@cityclass=cityclass from isgd_view where consultantno=@consultantno
select @agency=agency from consultant_accountinfo where consultantno=@consultantno
select @issign=issign from issign_view where @salarydate>=signdate and @salarydate<=resigndate and consultantno=@consultantno
select @ot=cast(ot as dec),@od=cast(od as dec),@deduction=cast(deduction as dec),
@bonus1=cast(bonus as dec),@typescode=typescode from duty where salarydate like @salarydate
and consultantno=@consultantno
if @ot is null
set @ot=0
if @od is null
set @od=0
if @deduction is null
set @deduction=0
if @bonus1 is null
set @bonus1=0
exec cpc_proc @consultantno,@salarydate
select @cpctotal=total,@n=n from cpc_total where cpc_date=@salarydate and consultantno=@consultantno
if @typescode = '101'
begin
set @i=@i+1
select @basesalary=amount,@bonus=bonus from levels where levelcode=@levels
set @true_salary=@basesalary*dbo.cpc_percent(@cpctotal)
--以下是实得资金代码
select @consultant_exponent=consultant_exponent from consultant_exponent_view where consultantno=@consultantno
if @consultant_exponent is null
set @true_bonus=0
else
set @true_bonus=@bonus*@consultant_exponent
set @od=(@true_salary/26)*@od*3
if @ot>167
set @ot=(@true_salary/167)*(@ot-167)*1.5
else
set @ot=-(@true_salary+@true_bonus)/167*(167-@ot)
set @sumsalary=@true_salary+@true_bonus+@od+@ot+@bonus1-@deduction
--最低保障
if @cityclass like 'A'
if @true_salary+@true_bonus<400
begin
set @od=(400/26)*@od*3
if @ot>167
set @ot=(400/167)*(@ot-167)*1.5
else
set @ot=-(400/167)*(167-@ot)
set @sumsalary=400+@od+@ot+@bonus1-@deduction
end
else
if @true_salary+@true_bonus<300
begin
set @od=(300/26)*@od*3
if @ot>167
set @ot=(300/167)*(@ot-167)*1.5
else
set @ot=-(300/167)*(167-@ot)
set @sumsalary=300+@od+@ot+@bonus1-@deduction
end
end
else if @typescode = '102'
begin
set @j=@j+1
select @basesalary=amount1,@bonus=bonus1 from levels where levelcode=@levels
set @true_salary=@basesalary
set @true_bonus=@bonus*dbo.bonus_modulus(@cpctotal)
set @od=(@basesalary/26)*@od*3
if @ot>167
set @ot=(@true_salary/167)*(@ot-167)*1.5
else
set @ot=-(@true_salary+@true_bonus)/167*(167-@ot)
set @sumsalary=@true_salary+@true_bonus+@od+@ot+@bonus1-@deduction
--最低保障
if @cityclass like 'A'
if @true_salary+@true_bonus<400
begin
set @od=(400/26)*@od*3
if @ot>167
set @ot=(400/167)*(@ot-167)*1.5
else
set @ot=-(400/167)*(167-@ot)
set @sumsalary=400+@od+@ot+@bonus1-@deduction
end
else
if @true_salary+@true_bonus<300
begin
set @od=(300/26)*@od*3
if @ot>167
set @ot=(300/167)*(@ot-167)*1.5
else
set @ot=-(300/167)*(167-@ot)
set @sumsalary=300+@od+@ot+@bonus1-@deduction
end
end
else if @typescode = '103'
begin
set @k=@k+1
select @basesalary=amount2 from levels where levelcode=@levels
set @true_salary=@basesalary*@ot*dbo.bonus_modulus(@cpctotal)
set @true_bonus=0
set @sumsalary=@true_salary+@true_bonus
end
else
begin
set @l=@l+1
set @sumsalary=0
set @true_salary=0
set @true_bonus=0
end if @issign='Y'
begin
select @comlostrate=comlostrate,@comhouserate=comhouserate,
@peroldrate=peroldrate,@perlostrate=perlostrate,@perhouserate=perhouserate
from salaryconsts
set @bcpay=@paysalary*(@peroldrate+@perlostrate+@perhouserate)
if @isgd='Y'
select @comoldrate=comoldRate,@combabyrate=combabyrate from salaryconsts
else
select @comoldrate=comoldRate1,@combabyrate=combabyrate1 from salaryconsts
set @welfare=@paysalary*(@comoldrate+@comlostrate+@comhouserate+@combabyrate)
end
else
begin
set @bcpay=0
set @welfare=0
set @peroldrate=0
set @perlostrate=0
set @perhouserate=0
end set @income=@sumsalary - @bcpay
if @income<=@taxpoint
set @sumtax=0
else
set @sumtax=@income - @taxpoint
set @taxrate=dbo.taxrate(@sumtax)
set @deductrate=dbo.deductrate(@sumtax)
if @sumtax=0
set @tax=0
else
set @tax=@sumtax * @taxrate - @deductrate
set @aftertax=@income - @tax
if @agency='达生'
set @managecost=@overhead
else
set @managecost=@overhead1
insert into consultant_salary1 (consultantno1,salarydate,basesalary,bonus,commision,ot,od,
deduction,sumsalary,old,lost,house,bcpay,income,sumtax,taxrate,deductrate,tax,
aftertax,welfare,managecost,paysalary,cpc_count)
values(@consultantno,@salarydate,@true_salary,@true_bonus,@bonus1,@ot,@od,@deduction,
@sumsalary,@peroldrate,@perlostrate,@perhouserate,@bcpay,@income,@sumtax,
@taxrate,@deductrate,@tax,@aftertax,@welfare,@managecost,@paysalary,@n)
fetch next from consultant_cur into @consultantno
end
close consultant_cur
deallocate consultant_cur
--select @i as 'i',@j as 'j',@k as 'k',@l as 'l'
end
GO没办法,程序只能写成这样,谢谢各位指点改进!!!!
当然也可以在select时加判断
不过我这个人比较懒
能省就省了
谢谢你指教,我再去改进!