[size=large]
CREATE PROCEDURE "DBA"."p_f_fp_fpcx"(@fpdmvar char(20),@fphmvar char(20)) as
declare @ii int --声明变量
begin
if(@fpdmvar='' or @fphmvar='')
begin
select fpdm,
fphm,
khmc,
khmc,
convert(char(10),kprq,111),
je,
case when tkqk=0 then 'use' else
case when tkqk=1 then 'used' else ''
end
end
from dw_ods.czfpmx a where 1!=1
return --直接返回
end
select @ii= isnull(count(*),0) from dw_ods.czfpmx a
where a.fpdm =@fpdmvar and a.fphm = @fphmvar --判断这张表有无记录
if ( length(@fpdmvar)=12 and (substring(@fpdmvar, 1, 5) = '23200' and substring(@fpdmvar, 8, 4) = '0011') )
begin
select fpzl_dm,fp_hm,'shuiwujiguan','khmc',
convert(char(10),lr_sj,111),je,'bs' from dw_ods.t_fp_mqkpmx a
where a.fp_hm =@fphmvar
end
else if(@ii>0)
begin
select fpdm,
fphm,
b.mc,
khmc,
convert(char(10),kprq,111),
je,
case when tkqk=0 then 'use' else
case when tkqk=1 then 'unuse' else ''
end
end
from dw_ods.czfpmx a ,dw_ods.t_dm_fp_fpzl b
where substring(a.fpdm,1,5)||substring(a.fpdm,8,4) *= b.fpzl_dm
and a.fpdm = @fpdmvar and a.fphm =@fphmvar
end
else
begin
select a.fpzl_dm,@fphmvar, b.mc,'khmc',
convert(char(10),a.lr_sj,111),je,'bs'
from dw_ods.T_FP_FPFSJLMX a,dw_ods.t_dm_fp_fpzl b
where a.fpzl_dm *= b.fpzl_dm
and a.fpzl_dm =(substring(@fpdmvar,1,5)||substring(@fpdmvar,8,4))
and a.fp_qshm<=convert(numeric,(@fphmvar)) --这些地方有些累,没办法类型要匹配的
and convert(numeric,@fphmvar)<=a.fp_zzhm
end
-------作用就是 从三张表中按先后顺序去取相应的记录,然后返回。
end
[/size]