IF @e<>0
insert into #Rec values(@WRDCDNAME,@a,@b,round(@b/@a,4) * 100,0,0,0,@e,@f,round(@f/@e,4) * 100)
else
insert into #Rec values(@WRDCDNAME,0,0,0,0,0,0,0,0,0)
insert into #Rec values(@WRDCDNAME,@a,@b,round(@b/@a,4) * 100,0,0,0,@e,@f,round(@f/@e,4) * 100)
else
insert into #Rec values(@WRDCDNAME,0,0,0,0,0,0,0,0,0)
郁闷我条件n多。嵌套怕出问题。!!
select ....在select里,就可以比较灵活的用case了
drop procedure mypro
go
create procedure mypro
(
@WRDCDNAME varchar(1000) ,
@a bigint ,
@b bigint,
@c bigint,
@d bigint,
@e bigint,
@f bigint
)
as
create table #Rec(col1 varchar(1000),col2 bigint,col3 bigint,col4 bigint,col5 bigint,col6 bigint,col7 bigint,col8 bigint,col9 bigint,col10 bigint)
declare @str varchar(8000)
declare @s varchar(8000)
set @str = '
insert into #Rec(col1,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9,col10)
select '
select @s = case
when (@a = 0) then @str +''''+@WRDCDNAME+''''+',0,0,0,0,0,0,0,0,0'
when (@c=0 and @a<>0 and @e<>0 ) then @str +''''+@WRDCDNAME+''''+','+convert(varchar(100),@a)+','+convert(varchar(100),@b)+',round('+convert(varchar(100),@b)+'/'+convert(varchar(100),@a)+',4) * 100,0,0,0,'+convert(varchar(100),@e)+','+convert(varchar(100),@f)+',round('+convert(varchar(100),@f)+'/'+convert(varchar(100),@e)+',4) * 100'
when (@c=0 and @e=0 ) then @str +''''+@WRDCDNAME+''''+',0,0,0,0,0,0,0,0,0'
end
exec(@s)-- if (@a = 0)
-- begin
-- set @s = @str +''''+@WRDCDNAME+''''+',0,0,0,0,0,0,0,0,0'
-- exec(@s)
-- end
-- if (@c=0 and @a<>0 and @e<>0 )
-- begin
-- set @s = @str +''''+@WRDCDNAME+''''+','+@a+','+@b+',round('+@b+'/'+@a+',4) * 100,0,0,0,'+@e+','+@f+',round('+@f+'/'+@e+',4) * 100'
-- exec(@s)
-- print @s
-- end
-- if (@c=0 and @e=0 )
-- begin
-- set @s = @str +''''+@WRDCDNAME+''''+',0,0,0,0,0,0,0,0,0'
-- exec(@s)
-- endselect * from #Rec
drop table #Rec
go
--exec mypro 'aaa',3,100,0,1,1,1