问题1: If @DisplayChar = '' or @DisplayChar is Null
Select id =identity(int,1,1),g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
into #tmp
From CR_RptItem
Else
Select id =identity(int,1,1),g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
into #tmp
From CR_RptItem Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0)
存储过程中的一段判断,在执行生成存储过程时会报错提示:
数据库中已存在名为 '#tmp ' 的对象。这个错误要如何修正?,另这个判断能不能再简化一些,我感觉写的太啰唆了~~
问题2
If @DisplayCase ='' or @DisplayCase is Null
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+@sqlend+@Sqlgroup+')'
)
Else
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+@sqlend+@DisplayCase+@Sqlgroup+')'
)这个问题也是,判断能不能再简化一些,我感觉写的太啰唆了~~
Select id =identity(int,1,1),g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
into #tmp
From CR_RptItem
Else
Select id =identity(int,1,1),g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
into #tmp
From CR_RptItem Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0)
存储过程中的一段判断,在执行生成存储过程时会报错提示:
数据库中已存在名为 '#tmp ' 的对象。这个错误要如何修正?,另这个判断能不能再简化一些,我感觉写的太啰唆了~~
问题2
If @DisplayCase ='' or @DisplayCase is Null
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+@sqlend+@Sqlgroup+')'
)
Else
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+@sqlend+@DisplayCase+@Sqlgroup+')'
)这个问题也是,判断能不能再简化一些,我感觉写的太啰唆了~~
-----简化后
create table #tmp (id int identity(1,1),g varchar(10),casechar varchar(1000))
declare @DisplayChar varchar(10),@sql1 varchar(1000),@sql2 varchar(1000)
select @sql1='
insert into #tmp Select g=0,
CaseChar=('',Sum(IsNull([''+FItemNum+''_qc],0)) As [''+FItemNum+''_qc]''+'',''+''Sum(IsNull([''+FItemNum+''_qm],0)) As [''+FItemNum+''_qm]'')
From CR_RptItem '
select @sql2=' Where FItemNum in (Select PaValue From Report_Planparameter Where charindex('',''+Ltrim(ID)+'','','',''+@DisplayChar+'','')>0)'If @DisplayChar = '' or @DisplayChar is Null
exec(@sql1)
Else
exec(@sql1+@sql2)
---修改#TMP 简化前create table #tmp (id int identity(1,1),g varchar(10),casechar varchar(1000))
declare @DisplayChar varchar(10)If @DisplayChar = '' or @DisplayChar is Null
insert into #tmp Select g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
From CR_RptItem
Else
insert into #tmp Select g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
From CR_RptItem Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',','+@DisplayChar+',')>0)
估计是你执行过了,没有删除
--前面加语句
if exists (select * from tempdb..sysobjects where xtype='u' and id=object_id('tempdb..#temp'))
drop table #temp
--(说明下:效率不如原来的)
if exists (select * from tempdb..sysobjects where xtype='u' and id=object_id('tempdb..#temp'))
drop table #temp Select id =identity(int,1,1),g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
into #tmp
From CR_RptItem
Where @DisplayChar = '' or @DisplayChar is Null or FItemNum in (
Select PaValue
From Report_Planparameter
Where charindex(','+Ltrim(ID)+',',','+isnull(@DisplayChar,'')+',')>0
)
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+@sqlend+isnull(@DisplayCase,'')+@Sqlgroup+')'
)
insert into #tmp Select g=0,
CaseChar=(',Sum(IsNull(['+FItemNum+'_qc],0)) As ['+FItemNum+'_qc]'+','+'Sum(IsNull(['+FItemNum+'_qm],0)) As ['+FItemNum+'_qm]')
From CR_RptItem Where FItemNum in (Select PaValue From Report_Planparameter Where charindex(','+Ltrim(ID)+',',',''1,2,3,4,5,6'',')>0),查询结果出来的并不完全正确,出来的是去头去尾的,应该是六条,结果只出了四条,
if exists (select * from tempdb..sysobjects where xtype= 'u ' and id=object_id( 'tempdb..#temp '))
drop table 'tempdb..#temp