CREATE PROCEDURE [dbo].[gzbgrsds]AS
BEGIN Declare @sds as float
Declare @ydze float,@ylj float,@gjj float,@yb float,@sybx float
Declare @sl float,@sskcs float,@num float
Declare @human_sid int
select @human_sid=human_sid,@ydze=yingdze,@ylj=ylj,@gjj=gjj,@yb=yb,@sybx=sybx from View_gzb
declare Hcursor CURSOR
for select human_sid,dysj from View_gzb open Hcursor
fetch next from Hcursor into @human_sid,@sds
while(@@FETCH_STATUS=0)
begin
select @num=yingdze-ylj-gjj-yb-sybx-3500 from View_gzb where human_sid=@human_sid
if @num<=0
begin
set @sl=0
set @sskcs=0
set @sds=0
end if @num>0 and @num<=1500
begin
set @sl=0.03
set @sskcs=0
set @sds=@num*@sl-@sskcs
end
if @num>1500 and @num<=4500
begin
set @sl=0.1
set @sskcs=105
set @sds=@num*@sl-@sskcs
end
if @num>4500 and @num<=9000
begin
set @sl=0.2
set @sskcs=555
set @sds=@num*@sl-@sskcs
end
if @num>9000 and @num<=35000
begin
set @sl=0.25
set @sskcs=1005
set @sds=@num*@sl-@sskcs
end
if @num>35000 and @num<=55000
begin
set @sl=0.3
set @sskcs=2755
set @sds=@num*@sl-@sskcs
end
if @num>55000 and @num<=80000
begin
set @sl=0.35
set @sskcs=5505
set @sds=@num*@sl-@sskcs
end
if @num>80000
begin
set @sskcs=13505
set @sds=@num*@sl-@sskcs
end update sa_gzb_zb set @sds=dysj where human_sid=@human_sid
fetch next from Hcursor into @human_sid,@sdsEND
CLOSE Hcursor
DEALLOCATE Hcursor
GO
用游标写了计算个人所得税的计算,不知道这个有什么语法问题,或者有什么更好的计算方式,求教育?
http://blog.csdn.net/maco_wang/article/details/6299279
正如dawugui所说。如果能用sql语句直接写出来的,就尽量直接写
CASE
WHEN XX <= 0 THEN 0
CASE
When XX …… Then ……
End