SQL存储过程IF ELSE分支用SELECT INTO临时表报已存在错误求助

问题原因 #

你遇到的错误根源是SQL Server的预编译机制：它会对整个批处理代码做语法和对象检查，哪怕ELSE分支实际不会执行，编译器也会检测到两个分支里都用SELECT INTO创建同一个临时表，因此提前抛出“对象已存在”的错误，和运行时是否走到对应分支无关。

修复方案 #

推荐两种解决方式，优先选择第一种：

方式1：提前创建临时表，分支内仅执行插入操作 #

先定义好临时表的结构，再在不同分支里用INSERT INTO填充数据，彻底避免重复创建的冲突：

DECLARE @Lotnum varchar(30);
DECLARE @FormulationID varchar(30); 
DECLARE @QtyToProduce numeric(18, 2); 
DECLARE @JobType varchar(2); 

DECLARE @TotalToDisp numeric(18, 2);
DECLARE @ContReq INT;
DECLARE @CONTCODE varchar(30);
DECLARE @CONTMAXQUANT INT;

SET @Lotnum = 'I23-01134';
SET @FormulationID = 'NPI-401-50F';
SET @QtyToProduce = 50;
SET @JobType = 'IN';

-- 先清理已存在的临时表，确保每次执行环境干净
IF OBJECT_ID('tempdb..#TempProdQuatities') IS NOT NULL
    DROP TABLE #TempProdQuatities;

-- 提前创建临时表，结构与SELECT结果完全匹配
CREATE TABLE #TempProdQuatities (
    BillNo varchar(30),
    CurrentBillRevision varchar(30),
    UDF_TOTPARTS numeric(18,2),
    UDF_SOLDBYGALLON bit, -- 请根据实际字段类型调整，此处为示例
    UDF_WPG numeric(18,2),
    UDF_DEFAULTBATCHSIZE numeric(18,2),
    ComponentItemCode varchar(30),
    UDF_PARTS numeric(18,2),
    QTYRECPERUM numeric(18,4),
    TotalQTY numeric(18,2),
    ExtendedQTY numeric(18,4),
    UDF_PAGEGRP INT,
    UDF_TUBGROUP INT,
    CondensedFormulationID varchar(16),
    LotNum varchar(30)
);
    
BEGIN
    IF @JobType = 'IN'
        BEGIN
        PRINT 'jOB tYPE IS iNK';

        INSERT INTO #TempProdQuatities
        SELECT 
            MAS_LPI.dbo.BM_BillHeader.BillNo, 
            MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision, 
            MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS, 
            MAS_LPI.dbo.BM_BillHeader.UDF_SOLDBYGALLON, 
            MAS_LPI.dbo.BM_BillHeader.UDF_WPG, 
            MAS_LPI.dbo.BM_BillHeader.UDF_DEFAULTBATCHSIZE, 
            MAS_LPI.dbo.BM_BillDetail.ComponentItemCode, 
            MAS_LPI.dbo.BM_BillDetail.UDF_PARTS,
            MAS_LPI.dbo.BM_BillDetail.UDF_PARTS / MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS AS QTYRECPERUM,
            @QtyToProduce as TotalQTY,
            ROUND((MAS_LPI.dbo.BM_BillDetail.UDF_PARTS / MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS) * MAS_LPI.dbo.BM_BillHeader.UDF_WPG * @QtyToProduce, 4) AS ExtendedQTY,
            MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP, 
            MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP,
            LEFT(MAS_LPI.dbo.BM_BillHeader.BillNo, 16) AS CondensedFormulationID,
            @Lotnum AS LotNum 
        FROM            
            MAS_LPI.dbo.BM_BillDetail  
        INNER JOIN
            MAS_LPI.dbo.BM_BillHeader ON MAS_LPI.dbo.BM_BillDetail.BillNo = MAS_LPI.dbo.BM_BillHeader.BillNo 
                                      AND MAS_LPI.dbo.BM_BillDetail.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision
        WHERE
            MAS_LPI.dbo.BM_BillHeader.BillNo = @FormulationID 
            AND MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP = 3 
            AND MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP = 500 
            AND MAS_LPI.dbo.BM_BillHeader.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision ;
    END
    ELSE 
    BEGIN
        PRINT 'jOB tYPE IS sOMETHING eLSE';
    
        INSERT INTO #TempProdQuatities
        SELECT 
            MAS_LPI.dbo.BM_BillHeader.BillNo, 
            MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision, 
            MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS, 
            MAS_LPI.dbo.BM_BillHeader.UDF_SOLDBYGALLON, 
            MAS_LPI.dbo.BM_BillHeader.UDF_WPG, MAS_LPI.dbo.BM_BillHeader.UDF_DEFAULTBATCHSIZE, 
            MAS_LPI.dbo.BM_BillDetail.ComponentItemCode, MAS_LPI.dbo.BM_BillDetail.UDF_PARTS,
            MAS_LPI.dbo.BM_BillDetail.UDF_PARTS/MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS as QTYRECPERUM,
            @QtyToProduce as TotalQTY,
            Round((MAS_LPI.dbo.BM_BillDetail.UDF_PARTS/MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS) * MAS_LPI.dbo.BM_BillHeader.UDF_WPG * @QtyToProduce,4) as ExtendedQTY,
            MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP, MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP,
            Left(MAS_LPI.dbo.BM_BillHeader.BillNo,16) as CondensedFormulationID,
            @Lotnum as LotNum 
        FROM            
            MAS_LPI.dbo.BM_BillDetail INNER JOIN
            MAS_LPI.dbo.BM_BillHeader ON MAS_LPI.dbo.BM_BillDetail.BillNo = MAS_LPI.dbo.BM_BillHeader.BillNo 
            AND MAS_LPI.dbo.BM_BillDetail.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision
            Where MAS_LPI.dbo.BM_BillHeader.BillNo = @FormulationID and MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP = 3 and MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP = 500 
            and MAS_LPI.dbo.BM_BillHeader.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision ;
        END
END

方式2：用动态SQL避开编译检查（不推荐，仅作备选） #

如果不想提前定义表结构，可以把分支内的逻辑改成动态SQL，这样编译器不会预检查动态代码里的对象：

DECLARE @Lotnum varchar(30);
DECLARE @FormulationID varchar(30); 
DECLARE @QtyToProduce numeric(18, 2); 
DECLARE @JobType varchar(2); 

DECLARE @TotalToDisp numeric(18, 2);
DECLARE @ContReq INT;
DECLARE @CONTCODE varchar(30);
DECLARE @CONTMAXQUANT INT;

SET @Lotnum = 'I23-01134';
SET @FormulationID = 'NPI-401-50F';
SET @QtyToProduce = 50;
SET @JobType = 'IN';

-- 先清理临时表
IF OBJECT_ID('tempdb..#TempProdQuatities') IS NOT NULL
    DROP TABLE #TempProdQuatities;
    
BEGIN
    IF @JobType = 'IN'
        BEGIN
        PRINT 'jOB tYPE IS iNK';
        EXEC('
            SELECT 
                MAS_LPI.dbo.BM_BillHeader.BillNo, 
                MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision, 
                MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS, 
                MAS_LPI.dbo.BM_BillHeader.UDF_SOLDBYGALLON, 
                MAS_LPI.dbo.BM_BillHeader.UDF_WPG, 
                MAS_LPI.dbo.BM_BillHeader.UDF_DEFAULTBATCHSIZE, 
                MAS_LPI.dbo.BM_BillDetail.ComponentItemCode, 
                MAS_LPI.dbo.BM_BillDetail.UDF_PARTS,
                MAS_LPI.dbo.BM_BillDetail.UDF_PARTS / MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS AS QTYRECPERUM,
                '+@QtyToProduce+' as TotalQTY,
                ROUND((MAS_LPI.dbo.BM_BillDetail.UDF_PARTS / MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS) * MAS_LPI.dbo.BM_BillHeader.UDF_WPG * '+@QtyToProduce+', 4) AS ExtendedQTY,
                MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP, 
                MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP,
                LEFT(MAS_LPI.dbo.BM_BillHeader.BillNo, 16) AS CondensedFormulationID,
                '''+@Lotnum+''' AS LotNum 
            INTO
                #TempProdQuatities
            FROM            
                MAS_LPI.dbo.BM_BillDetail  
            INNER JOIN
                MAS_LPI.dbo.BM_BillHeader ON MAS_LPI.dbo.BM_BillDetail.BillNo = MAS_LPI.dbo.BM_BillHeader.BillNo 
                                          AND MAS_LPI.dbo.BM_BillDetail.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision
            WHERE
                MAS_LPI.dbo.BM_BillHeader.BillNo = '''+@FormulationID+''' 
                AND MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP = 3 
                AND MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP = 500 
                AND MAS_LPI.dbo.BM_BillHeader.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision ;
        ')
    END
    ELSE 
    BEGIN
        PRINT 'jOB tYPE IS sOMETHING eLSE';
        EXEC('
            SELECT 
                MAS_LPI.dbo.BM_BillHeader.BillNo, 
                MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision, 
                MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS, 
                MAS_LPI.dbo.BM_BillHeader.UDF_SOLDBYGALLON, 
                MAS_LPI.dbo.BM_BillHeader.UDF_WPG, MAS_LPI.dbo.BM_BillHeader.UDF_DEFAULTBATCHSIZE, 
                MAS_LPI.dbo.BM_BillDetail.ComponentItemCode, MAS_LPI.dbo.BM_BillDetail.UDF_PARTS,
                MAS_LPI.dbo.BM_BillDetail.UDF_PARTS/MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS as QTYRECPERUM,
                '+@QtyToProduce+' as TotalQTY,
                Round((MAS_LPI.dbo.BM_BillDetail.UDF_PARTS/MAS_LPI.dbo.BM_BillHeader.UDF_TOTPARTS) * MAS_LPI.dbo.BM_BillHeader.UDF_WPG * '+@QtyToProduce+',4) as ExtendedQTY,
                MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP, MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP,
                Left(MAS_LPI.dbo.BM_BillHeader.BillNo,16) as CondensedFormulationID,
                '''+@Lotnum+''' as LotNum 
                into #TempProdQuatities
            FROM            
                MAS_LPI.dbo.BM_BillDetail INNER JOIN
                MAS_LPI.dbo.BM_BillHeader ON MAS_LPI.dbo.BM_BillDetail.BillNo = MAS_LPI.dbo.BM_BillHeader.BillNo 
                AND MAS_LPI.dbo.BM_BillDetail.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision
                Where MAS_LPI.dbo.BM_BillHeader.BillNo = '''+@FormulationID+''' and MAS_LPI.dbo.BM_BillDetail.UDF_PAGEGRP = 3 and MAS_LPI.dbo.BM_BillDetail.UDF_TUBGROUP = 500 
                and MAS_LPI.dbo.BM_BillHeader.Revision = MAS_LPI.dbo.BM_BillHeader.CurrentBillRevision ;
        ')
        END
END

注意：动态SQL存在SQL注入风险，调试难度也更高，仅在特殊场景下使用。

关键说明 #

• 你之前添加的“删除临时表”操作如果放在分支内部，完全起不到作用——因为编译阶段就已经报错，根本到不了执行删除的步骤。必须把删除操作放在所有分支代码之前。
• 方式1的核心是让临时表只被创建一次，后续分支仅执行插入操作，彻底规避编译时的对象冲突问题。

内容的提问来源于stack exchange，提问作者Ryan Potratz