CREATE procedure dep_zixun
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
As
exec('
Select * into #tmp from usr_incidents
where ts_subdep='+@Dep+' and ts_projectid in ('+@project+') and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''
select a.ts_name, count(ts_questiontype)as 个数,
cast(ceiling(count(ts_questiontype)*1.0/(select count(1)
from #tmp )*100) as varchar(3))+''%'' as 百分比 from ts_selections a
left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_name
drop table #tmp ')
GO#TMP的记录数有可能会是0,所以系统报了被0除的错,改怎么判断#tmp的记录数呢?
case when (select count(1) from #tmp )=0 then 1 else (select count(1) from #tmp ) end
(@Dep nvarchar(20),
@project nvarchar(20),
@StartDate nvarchar(20),
@EndDate nvarchar(20))
As
exec('
Select * into #tmp from usr_incidents
where ts_subdep='+@Dep+' and ts_projectid in ('+@project+') and
ts_submitdate between '''+@StartDate+''' and '''+@EndDate+'''
select a.ts_name, count(ts_questiontype)as 个数,
cast(ceiling(count(ts_questiontype)*1.0/(select top 1 case when (select count(1) from #tmp)=0 then 1 else (select count(1) from #tmp) end from #tmp)*100) as varchar(3))+''%'' as 百分比 from ts_selections a
left join #tmp b on a.ts_id=b.ts_questiontype
where a.ts_fldid=147
group by b.ts_questiontype,a.ts_name
drop table #tmp ')
GO