CREATE PROCEDURE dbo.sp_surplus
@scodedate char(14),
@is_maxdate char(8),
@dec_currsurplus decimal(2),
@dec_surplus decimal(2),
@il_cnt integer ,
@is_year char(4),
@is_month char(2),
@is_day char(2),
@is_search char(8)
AS
declare @is_code char(6),@is_date char(8),@dec_addsurplus decimal(2),@dec_reducesurplus decimal(2)set @is_code = left(@scodedate,6)
set @is_date = right(@scodedate,8)/*将所有的标志首先都置为正常缴交'00'*/
update dw
set state = '00'
where state not in ('00','01','02','03','04') /*计算单位当年增加额*/
select @dec_addsurplus = sum(fse)
from view_dwl
where flag not in ('15','16','17','00') and code = @is_code
/*计算单位当年减少额*/
select @dec_reducesurplus = sum(fse)
from view_dwl
where flag in ('15','16','17') and code = @is_code
/*计算单位的往年余额*/
select @dec_currsurplus = sum(fse)
from view_dwl
where flag = '00' and code = @is_code
/*计算单位的公积金余额*/
set @dec_surplus = @dec_addsurplus - @dec_reducesurplus + @dec_currsurplus/*余额为零*/
if @dec_surplus = 0
update dw
set state = '01'
where code = @is_code
return/*以下为余额不为零的统计从未发生过较缴流水的用户*/
select @il_cnt =count(*)
from view_dwl
where code=@is_code and ((flag between '01' and '12') or flag = '20')if @il_cnt = 0
update dw
set state = '02'
where code = @is_code
return
/*发生过缴交流水的*/
select @is_maxdate = max(date)
from view_dwl
where code = @is_code set @is_year = left(@is_date,4)
set @is_month = substring(@is_date,5,2)
set @is_day = right(@is_date,2)/*当月往上7个月内缴交流水的,但是不能按时缴交公积金的*/
if @is_month >= '07'
set @is_search = @is_year + right('0' + cast(cast(@is_month as int)- 6 as varchar(100)),2) + @is_day
else
set @is_search = cast(cast(@is_year as int)-1 as varchar(100)) + right('0'+ cast(cast(@is_month as int)+6 as varchar(100)),2) + @is_day
select @il_cnt = count(*)
from view_dwl
where code = @is_code and ((flag >='01' and flag <= '12') or flag = '20') and date >= @is_search
if @il_cnt <> 0
update dw
set state = '03'
where code = @is_code
return/*当月往上7个月内缴交流水的,但是不能按时缴交公积金的*/
if @is_month = '12'
set @is_search = @is_year + '0101'
else
set @is_search = cast(cast(@is_year as int)-1 as varchar(100)) + right('0'+ cast(cast(@is_month as int)+1 as varchar(100)),2) + @is_dayselect @il_cnt = count(*)
from view_dwl
where code = @is_code and ((flag >='01' and flag <= '12') or flag = '20') and date >= @is_search
if @il_cnt <> 0
update dw
set state ='04'
where code = @is_code
return
@scodedate char(14),
@is_maxdate char(8),
@dec_currsurplus decimal(2),
@dec_surplus decimal(2),
@il_cnt integer ,
@is_year char(4),
@is_month char(2),
@is_day char(2),
@is_search char(8)
AS
declare @is_code char(6),@is_date char(8),@dec_addsurplus decimal(2),@dec_reducesurplus decimal(2)set @is_code = left(@scodedate,6)
set @is_date = right(@scodedate,8)/*将所有的标志首先都置为正常缴交'00'*/
update dw
set state = '00'
where state not in ('00','01','02','03','04') /*计算单位当年增加额*/
select @dec_addsurplus = sum(fse)
from view_dwl
where flag not in ('15','16','17','00') and code = @is_code
/*计算单位当年减少额*/
select @dec_reducesurplus = sum(fse)
from view_dwl
where flag in ('15','16','17') and code = @is_code
/*计算单位的往年余额*/
select @dec_currsurplus = sum(fse)
from view_dwl
where flag = '00' and code = @is_code
/*计算单位的公积金余额*/
set @dec_surplus = @dec_addsurplus - @dec_reducesurplus + @dec_currsurplus/*余额为零*/
if @dec_surplus = 0
update dw
set state = '01'
where code = @is_code
return/*以下为余额不为零的统计从未发生过较缴流水的用户*/
select @il_cnt =count(*)
from view_dwl
where code=@is_code and ((flag between '01' and '12') or flag = '20')if @il_cnt = 0
update dw
set state = '02'
where code = @is_code
return
/*发生过缴交流水的*/
select @is_maxdate = max(date)
from view_dwl
where code = @is_code set @is_year = left(@is_date,4)
set @is_month = substring(@is_date,5,2)
set @is_day = right(@is_date,2)/*当月往上7个月内缴交流水的,但是不能按时缴交公积金的*/
if @is_month >= '07'
set @is_search = @is_year + right('0' + cast(cast(@is_month as int)- 6 as varchar(100)),2) + @is_day
else
set @is_search = cast(cast(@is_year as int)-1 as varchar(100)) + right('0'+ cast(cast(@is_month as int)+6 as varchar(100)),2) + @is_day
select @il_cnt = count(*)
from view_dwl
where code = @is_code and ((flag >='01' and flag <= '12') or flag = '20') and date >= @is_search
if @il_cnt <> 0
update dw
set state = '03'
where code = @is_code
return/*当月往上7个月内缴交流水的,但是不能按时缴交公积金的*/
if @is_month = '12'
set @is_search = @is_year + '0101'
else
set @is_search = cast(cast(@is_year as int)-1 as varchar(100)) + right('0'+ cast(cast(@is_month as int)+1 as varchar(100)),2) + @is_dayselect @il_cnt = count(*)
from view_dwl
where code = @is_code and ((flag >='01' and flag <= '12') or flag = '20') and date >= @is_search
if @il_cnt <> 0
update dw
set state ='04'
where code = @is_code
return
@scodedate char(14), ------as 上面的都是在调用的时候要指定的
@is_maxdate char(8),
@dec_currsurplus decimal(2),
@dec_surplus decimal(2),
@il_cnt integer ,
@is_year char(4),
@is_month char(2),
@is_day char(2),
@is_search char(8)
AS
------declare 才是声明内部变量!我只改了几个,你自己再改!
declare @is_code char(6),@is_date char(8),@dec_addsurplus decimal(2),@dec_reducesurplus decimal(2)
@scodedate char(14)
@is_code char(6),
@is_date char(8),
@is_maxdate char(8),
@dec_addsurplus decimal{2},
@dec_reducesurplus decimal{2},
@dec_currsurplus decimal{2},
@dec_surplus decimal{2},
@il_cnt integer ,
@is_year char(4),
@is_month char(2),
@is_day char(2),
@is_search char(8)其他的错误再慢慢找吧,仔细点!要避免出这样的语法错误,教你一招这么写永远不会出这总低级语法错误!
CREATE PROCEDURE dbo.sp_surplus
@scodedate char(14)
,@is_code char(6)
,@is_date char(8)
,@is_maxdate char(8)
,@dec_addsurplus decimal{2}
,@dec_reducesurplus decimal{2}
,@dec_currsurplus decimal{2}
,@dec_surplus decimal{2}
,@il_cnt integer
,@is_year char(4)
,@is_month char(2)
,@is_day char(2)
,@is_search char(8)在定义变量时把,号写在前面,则永远不会漏写!