T-SQL与IF逻辑不与临时表工作(T-SQL with IF logic not working

IF OBJECT_ID('tempdb..#iftry') IS NOT NULL 
DROP TABLE #iftry

IF OBJECT_ID('BIC_Analytics.dbo.AdjudicateAllDCCharteredClaims') IS  NULL
begin
select 'this is start of first block'
 SELECT 'this is first block' as blockid
 INTO #iftry
select 'this is end of first block'
end

ELSE

begin
select 'this is start of 2nd block'
 SELECT 'this is 2nd block' as blockid
    INTO #iftry
select 'this is end of 2nd block'
end

select ':)'

select * from #iftry

一直给我的错误：

Msg 2714, Level 16, State 1, Line 18
There is already an object named '#iftry' in the database.

现在，它的工作原理

IF OBJECT_ID('tempdb..#iftry') IS NOT NULL 
DROP TABLE #iftry

create table #iftry (blockid varchar(20))


IF OBJECT_ID('BIC_Analytics.dbo.AdjudicateAllDCCharteredClaims') IS NOT NULL
begin
--select 'this is start of first block'
 insert into #iftry (blockid)
 select 'this is first block' 
--select 'this is end of first block'
end

ELSE

begin
--select 'this is start of 2nd block'
 insert into #iftry (blockid)
 select 'this is 2nd block' 
--select 'this is end of 2nd block'
end

select ':)'

select * from #iftry

Answer 1:

这是一个分析问题，而不是运行时的问题。 SQL Server不能看到有不能达到两个代码路径。

要解决它，创建#temp表一次达阵：

SELECT 'bogus' INTO #iftry
  WHERE 1 = 0; -- creates empty table

IF ...
  INSERT #iftry ...
ELSE ...
  INSERT #iftry ...

有没有办法告诉SQL Server不要，除非你把两个#table声明分批（你不能真正做到）以这种方式工作，或建立动态SQL，并在该范围与#TEMP工作表中（不好玩）。

文章来源: T-SQL with IF logic not working with temp table