使用WITH公共表表达式的无效对象

本文关键字：无效 对象 表达式 WITH 使用 | 更新日期: 2023-09-27 18:05:23

我试图在存储过程中使用With 

    USE [BusOprtn]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER  PROCEDURE [dbo].[AddRepairedStock]
     @RepairedItems [dbo].[RepairedItems] readonly

    AS
    BEGIN 
    declare @productNo bigint,@productManufacturer bigint
      SET NOCOUNT ON;
      begin try
      begin transaction
      UPDATE [BusOprtn].[dbo].[RepairItem] SET [ReturnedQuantity] = rdi.ReturnedQuantity,[AmountPaid] = rdi.AmountPaid,[ReturnDate] = rdi.ReturnDate from [BusOprtn].[dbo].[RepairItem] as ri inner join @RepairedItems as rdi on ri.id=rdi.id;
     ;with y as (
     select [PartUsedId],rdi.[ReturnedQuantity] from [BusOprtn].[dbo].[RepairItem] as ri inner join @RepairedItems as rdi on ri.Id=rdi.id 
     ), x as (
     SELECT  [PartNo] ,[ManufacturerId],[ReturnedQuantity] FROM [BusOprtn].[dbo].[PartUsed] as p inner join y 
     on p.id = y.PartUsedId
     )

      UPDATE [BusOprtn].[dbo].[ProductMaster] SET   [RepairedStock] =( [RepairedStock]+x.[ReturnedQuantity]) from [BusOprtn].[dbo].[ProductMaster] as pm inner join x on x.[PartNo]=pm.Id;
      UPDATE [BusOprtn].[dbo].[ProductStockManufacturer] SET  [RepairedCurrentStock] = ([RepairedCurrentStock]+x.[ReturnedQuantity]) from [BusOprtn].[dbo].[ProductStockManufacturer] as pm inner join x on x.[PartNo]=pm.[ProductNo] and x.[ManufacturerId]= pm.[ManufacturerId];
      commit transaction
        end try
        BEGIN CATCH
        declare @ErrorMessage nvarchar(max), @ErrorSeverity 
int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast
(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();
    rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
        END CATCH
END

当我执行上面的命令时，它执行成功。但是当我尝试在运行时调用存储过程时，它会给出错误

`Invalid object name 'x'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.`

先前的问题是子查询，因为它不允许使用"with"，所以尝试使用另一个临时表y，但错误继续。

使用WITH公共表表达式的无效对象

这个错误来自于使用CTE x

的第二个更新语句。

CTE的作用域是一个语句，所以x只在第一次更新时可见。

要解决这个问题，您可以在第二次更新之前复制CTE的代码，或者您可以使用临时表(或表变量)来捕获x的输出，并在两个更新语句中使用临时表而不是CTE。

使用不同格式的代码，这很容易看到。

第一个update语句:

with y as 
(
  select [PartUsedId],
         rdi.[ReturnedQuantity] 
  from [BusOprtn].[dbo].[RepairItem] as ri 
    inner join @RepairedItems as rdi 
      on ri.Id=rdi.id 
), x as
(
  SELECT [PartNo],
         [ManufacturerId],
         [ReturnedQuantity] 
  FROM [BusOprtn].[dbo].[PartUsed] as p 
    inner join y 
      on p.id = y.PartUsedId
)
UPDATE [BusOprtn].[dbo].[ProductMaster] 
SET [RepairedStock] = ([RepairedStock]+x.[ReturnedQuantity]) 
from [BusOprtn].[dbo].[ProductMaster] as pm 
  inner join x 
    on x.[PartNo]=pm.Id;

第二个update语句:

UPDATE [BusOprtn].[dbo].[ProductStockManufacturer] 
SET  [RepairedCurrentStock] = ([RepairedCurrentStock]+x.[ReturnedQuantity]) 
from [BusOprtn].[dbo].[ProductStockManufacturer] as pm 
  inner join x 
    on x.[PartNo]=pm.[ProductNo] and x.[ManufacturerId]= pm.[ManufacturerId];

您可以尝试使用SQL Server Profiler (Service菜单中的MSSMS)。在那里，您可以找到确切接收到的查询。

相关文章：