问题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' 的对象。这个错误要如何修正?,另这个判断能不能再简化一些,我感觉写的太啰唆了~~
问题2If @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' 的对象。这个错误要如何修正?,另这个判断能不能再简化一些,我感觉写的太啰唆了~~
问题2If @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+')'
)
这个问题也是,判断能不能再简化一些,我感觉写的太啰唆了~~
drop table tb
go
if (...)....
into #tmp1
...
else
...into #tmp2
...就可以解决。
或者先建立临时表#tmp,插入数据的时候使用语句INERT INTO #tmp1 SELECT ...格式即可。
改成这样吧:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tmp]
GO
If isnull(@DisplayChar,'') = ''
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)第二问跟上面一样。将If @DisplayCase ='' or @DisplayCase is Null
改为If isnull(@DisplayCase ,'') = ''
]select * from #tmp
--报错
/*服务器: 消息 208,级别 16,状态 1,行 1
对象名 '#tmp' 无效。*/
--其实根被就没有表#tmp
declare @DisplayChar varchar(10)
If @DisplayChar = '' or @DisplayChar is Null
select top 1 * into #tmp from a
Else
select top 2 * into #tmp from a---报错
/*服务器: 消息 2714,级别 16,状态 1,行 5
数据库中已存在名为 '#tmp' 的对象。*/declare @DisplayChar varchar(10)
If @DisplayChar = '' or @DisplayChar is Null
select top 1 * into #tmp1 from a
Else
select top 2 * into #tmp2 from a--不报错
/*(所影响的行数为 1 行)*/
看下执行计划就知道了
因为在SQL语句执行以前会先生成一个执行计划,在执行计划里 #tmp1 与 #tmp2 是同时建的
当执行#tmp时所以会报/*服务器: 消息 2714,级别 16,状态 1,行 5
数据库中已存在名为 '#tmp' 的对象。*/ 的错