具有错误消息的存储过程中出现错误，因为已经有一个名为

Question

请帮助我调试以下存储过程的错误。当我试图执行收到错误的sp时，

(142行受影响) Msg 2714，级别16，状态6，行16，数据库中已经有一个名为“AOP_Monthly_GrowthRate_Acquisition”的对象。

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 
  'AOP_Monthly_GrowthRate_Acquisition' AND TABLE_SCHEMA = 'dbo')
    drop table BudgetWorld_temp.dbo.AOP_Monthly_GrowthRate_Acquisition

GO

SELECT               dbo.LTM_ACQUISITION_MONTHLY.Year, 
                     dbo.LTM_ACQUISITION_MONTHLY.Month, 
                     dbo.LTM_ACQUISITION_MONTHLY.SALES_MANAGER_CODE, 
                     dbo.LTM_ACQUISITION_MONTHLY.SALES_GROUP, 
                    dbo.LTM_ACQUISITION_MONTHLY.NetProductSales, 
                     dbo.LTM_ACQUISITION_MONTHLY.AverageCostBasedProfit, 
                     dbo.LTM_ACQUISITION_MONTHLY.ReplacementCostBasedProfit, 
            isnull(dbo.[Provided 2016 AOP Values_Acquisition].[AOP Sales],0) as 'AOP Sales', 
                     isnull(dbo.[Provided 2016 AOP Values_Acquisition].[AOP   
Profit Based On Average Cost],0) as 'AOP Profit Based On Average Cost'
INTO #TEMP3
FROM            dbo.LTM_ACQUISITION_MONTHLY LEFT OUTER JOIN
                         dbo.[Provided 2016 AOP Values_Acquisition] ON   
dbo.LTM_ACQUISITION_MONTHLY.Month = dbo.[Provided 2016 AOP   
Values_Acquisition].Month AND 
                         dbo.LTM_ACQUISITION_MONTHLY.SALES_MANAGER_CODE = 
dbo.[Provided 2016 AOP Values_Acquisition].[Sales Manager] AND 
                         dbo.LTM_ACQUISITION_MONTHLY.SALES_GROUP = dbo.  
[Provided 2016 AOP Values_Acquisition].[Sales Group]

SELECT *,
SALES_GROWTH = CASE  
  WHEN ([NetProductSales] + [AOP Sales]) = 0 THEN 0
  WHEN ([AOP Sales] ) > 0 AND ([NetProductSales]) = 0 THEN 1
  ELSE
  (([AOP Sales] -[NetProductSales])/[NetProductSales]) END
    ,
  Margin_Growth = 

  CASE  
  WHEN ([AverageCostBasedProfit] + [AOP Profit Based On Average Cost]) = 0   
THEN 0
  WHEN ([AOP Profit Based On Average Cost] ) > 0 AND   
([AverageCostBasedProfit]) = 0 THEN 1

  else
 (([AOP Profit Based On Average Cost] -  
[AverageCostBasedProfit])/[AverageCostBasedProfit]) END

 INTO AOP_Monthly_GrowthRate_Acquisition

FROM #TEMP3
DROP TABLE #TEMP3

谢谢你一吨

Answer 1

USE BudgetWorld_temp
GO

IF OBJECT_ID('dbo.AOP_Monthly_GrowthRate_Acquisition', 'U') IS NOT NULL
    DROP TABLE dbo.AOP_Monthly_GrowthRate_Acquisition
GO

IF OBJECT_ID('tempdb.dbo.#TEMP3') IS NOT NULL
    DROP TABLE #TEMP3
GO

SELECT
    a.[Year], 
    a.[Month], 
    a.SALES_MANAGER_CODE, 
    a.SALES_GROUP, 
    a.NetProductSales, 
    a.AverageCostBasedProfit, 
    a.ReplacementCostBasedProfit, 
    ISNULL(b.[AOP Sales], 0) AS [AOP Sales], 
    ISNULL(b.[AOP Profit Based On Average Cost], 0) AS [AOP Profit Based On Average Cost]
INTO #TEMP3
FROM dbo.LTM_ACQUISITION_MONTHLY a
LEFT JOIN dbo.[Provided 2016 AOP Values_Acquisition] b ON a.[Month] = b.[Month]
    AND a.SALES_MANAGER_CODE = b.[Sales Manager]
    AND a.SALES_GROUP = b.[Sales Group]

SELECT *
    , SALES_GROWTH =
        CASE  
            WHEN [NetProductSales] + [AOP Sales] = 0 THEN 0
            WHEN [AOP Sales] > 0 AND [NetProductSales] = 0 THEN 1
            ELSE (([AOP Sales] -[NetProductSales])/[NetProductSales])
        END
    , Margin_Growth = 
        CASE  
            WHEN ([AverageCostBasedProfit] + [AOP Profit Based On Average Cost]) = 0 THEN 0
            WHEN ([AOP Profit Based On Average Cost] ) > 0 AND ([AverageCostBasedProfit]) = 0 THEN 1
            ELSE (([AOP Profit Based On Average Cost] - [AverageCostBasedProfit])/[AverageCostBasedProfit])
        END
INTO dbo.AOP_Monthly_GrowthRate_Acquisition
FROM #TEMP3

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

Answer 2

从中选择

创建一个表，如果它已经退出(可能是从以前的执行中)，它就不能这样做。

您可以切换到临时表(前缀表名与#)。或者你可以用INSERT INTO代替。使用第二种方法，您可能需要在填充之前清除表。

Answer 3

可能您的用户有不同的默认架构。尝试将架构名称放在这里

 INTO **dbo**.AOP_Monthly_GrowthRate_Acquisition