“無效的列名x”和“資料庫中已經有一個名為'x'的物件”-有解無憂

我正在使用 Swagger API v1 OAS3 使用 T-SQL 在 SSMS v15.0.18386 中執行存盤程序。然而，奇怪的是，即使我在服務器回應中收到一條錯誤訊息，直接通過我的 Swagger UI 執行它仍然可以作業并給我預期的結果。我認為這與我創建和洗掉臨時表以及在洗掉之前為每個臨時表創建一個新列的方式有關。我想忽略錯誤訊息，但我認為這導致我的前端拒絕讓它作業。

我通過 Swagger 從我的服務器收到的錯誤訊息：

Microsoft.Data.SqlClient.SqlException (0x80131904)：
列名“類別”無效。
列名“類別”無效。
列名“類別”無效。
列名“類別”無效。
資料庫中已經有一個名為“rscopy”的物件。

在 Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)在 Microsoft.Data.SqlClient.TdsParser 的 Microsoft.Data.SqlClient.SqlConnection.OnError（SqlException例外，布爾 breakConnection，操作1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction）。 TryRun（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）在 Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，RunBehavior runBehavior，字串 resetOptionsString，Boolean isInternal，Boolean forDescribeParameterEncryption，Boolean shouldCacheForAlwaysEncrypted）

在 Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior，RunBehavior runBehavior，布爾 returnStream，布爾 isAsync，Int32 超時，任務和任務，布爾 asyncWrite，布爾 inRetry，SqlDataReader ds，布爾 describeParameterEncryptionRequest）
在 Microsoft.Data.SqlClient.SqlCommand。 RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，布爾回傳流，TaskCompletionSource 1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 完成，布爾 sendToPipe，Int32 超時，布爾和 usedCache，布爾 asyncWrite，布爾 inRetry，字串方法名）
在 Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
在 Microsoft.EntityFrameworkCore。 Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
在 Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable 1 parameters) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, Object[] parameters) at API.Controllers.CruiseLineController.CopyShip(Int32 shipId, String newShipName, String newShipCode) in CLController.cs:line 87 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
在 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker 呼叫者，任務 lastTask，下一個狀態，范圍范圍，物件狀態，布林值isCompleted)
在 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed 背景關系)
在 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() 處的 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) --- 從上一個位置結束堆疊跟蹤 - -- 在 Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0（ResourceInvoker 呼叫程式，任務 lastTask，下一個狀態，作用域范圍，物件狀態，布爾 isCompleted）在 Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0（ ResourceInvoker 呼叫程式，任務任務，IDisposable 范圍）在 Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0（端點端點，任務 requestTask，ILogger 記錄器）在 Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke（HttpContext 背景關系）在 Swashbuckle.AspNetCore。招搖UI。SwaggerUIMiddleware.Invoke(HttpContext httpContext) 在 Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) 在 Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context) 錯誤號：207，狀態：1，類：16

我的 T-SQL 代碼：

ALTER PROCEDURE [dbo].[CopyShip]
    (@ShipId int,
     @ShipName nvarchar(150),
     @ShipCode nvarchar(8))
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @CruiselineId int, @NewShipId int;

    SET @CruiselineId = (SELECT [CruiselineId] FROM [dbo].Ships 
                         WHERE ShipId = @ShipId);

    ---- CREATING COPY OF Ships
    SELECT * 
    INTO shicopy
    FROM [dbo].Ships 
    WHERE ShipId = @ShipId

    UPDATE shicopy SET [Name] = @ShipName
    UPDATE shicopy SET [ShipCode] = @ShipCode

    INSERT INTO Ships 
        SELECT
            [CruiselineId], [Name], [ShipCode], [ClassId],
            [guestNumber], [staffNumber], [creationYear],
            [weight], [length], [passagerDeck], [handicapCabins],
            [nationality]
        FROM shicopy

    DROP TABLE shicopy;

    -- Declaring a new int variable and setting its value to be 
    -- the highest number in the ShipId column of the Ships 
    -- table (a.k.a. the new ShipId of the newly created Ship).
    
    --DECLARE @NewShipId int;
    SET @NewShipId = (SELECT MAX(ShipId) FROM [dbo].[Ships]);

    -- CREATING COPY OF Cabins
    SELECT * 
    INTO cccopy
    FROM [dbo].CabinCategory 
    WHERE ShipId = @ShipId

    UPDATE cccopy SET ShipId = @NewShipId

    INSERT INTO CabinCategory 
        SELECT
            [ShipId], [CabinType], [BalconySize], [MinSize], [MaxSize],
            [NoOfBeds], [Category], [HexCodes], [Description],
            [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
            [HeaderDescriptionSE], [HeaderDescriptionNO],
            [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO],
            [CabinCategoryIdOld]
        FROM cccopy

    DROP TABLE cccopy;

    -- CREATING COPY OF BENEFITS
    SELECT * 
    INTO cbcopy
    FROM [dbo].[CabinToBenefits] 
    WHERE ShipId = @ShipId

    UPDATE cbcopy SET ShipId = @NewShipId

    ALTER TABLE cbcopy
        ADD Category nvarchar(255);

    EXEC (
    'Update B 
    set
    B.[Category] = bse.[Category]
    from dbo.[cbcopy] B 
    Inner join 
    dbo.CabinCategory bse on B.CabinCategoryId = BSE.CabinCategoryId

    Update B 
    set
    B.CabinCategoryId = bse.CabinCategoryId 
    from dbo.cbcopy B 
    Inner join 
    dbo.CabinCategory bse on B.Category = BSE.Category and B.ShipId = BSE.ShipId'
    )

    INSERT INTO CabinToBenefits
    SELECT [BenefitsId]
      ,[CabinCategoryId]
      ,[ShipId] FROM cbcopy

    DROP TABLE cbcopy;

...
...

---- CREATING COPY OF RestaurantToSeating

    Select * into rscopy
    FROM [dbo].[RestaurantToSeating] where ShipId = @ShipId

    update rscopy set ShipId = @NewShipId

    ALTER TABLE rscopy
    ADD Category nvarchar(255);

    EXEC (
    'Update B 
    set
    B.[Category] = bse.[Category]
    from dbo.rscopy B 
    Inner join 
    dbo.Restaurant bse on B.RestaurantId = BSE.RestaurantId

    Update B 
    set
    B.RestaurantId = bse.RestaurantId
    from dbo.rscopy B 
    Inner join 
    dbo.Restaurant bse on B.Category = BSE.Category and B.ShipId = BSE.ShipId'
    )


    INSERT INTO RestaurantToSeating
    SELECT [RestaurantId]
      ,[SeatingId]
      ,[ShipID] FROM rscopy

    DROP TABLE rscopy;
END

如您所見，除了錯誤訊息 ( ) 中提到的臨時表之外，我使用相同的代碼創建其他臨時表'rscopy'，但它是唯一一個帶有下劃線的錯誤。也許是因為它是我在存盤程序中創建的最后一個臨時表（帶有多對多變數）？

無論哪種方式，鑒于我所擁有的，我不太明白為什么我的代碼會導致我出現這個錯誤——尤其是當我通過我的 API UI 執行它時它實際作業時。

有任何想法嗎？

uj5u.com熱心網友回復：

不要復制到臨時表中，然后再將其洗掉。直接從原件插入即可。

還：

不要IDENTITY用SELECT MAX. 使用適當的IDENTITY列并使用OUTPUTor獲取先前的值SCOPE_IDENTITY()。
不要在不需要[]時參考每個列名。我沒有打擾洗掉它們，因為我一整天都沒有。
我必須說，我不清楚你為什么有這些連接，它們似乎沒有必要，但我把它們留在了里面。

CREATE OR ALTER PROCEDURE [dbo].[CopyShip]
     @ShipId int,
     @ShipName nvarchar(150),
     @ShipCode nvarchar(8)
AS

SET NOCOUNT ON;

INSERT INTO Ships (
  [CruiselineId], [Name], [ShipCode], [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
)
SELECT
  [CruiselineId], @ShipName, @ShipCode, [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
FROM [dbo].Ships 
WHERE ShipId = @ShipId;

DECLARE @NewShipId int = SCOPE_IDENTITY();

-- CREATING COPY OF Cabins
INSERT INTO CabinCategory (
  [ShipId], [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO], [CabinCategoryIdOld]
)
SELECT
  @NewShipId, [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO],
  [CabinCategoryIdOld]
FROM [dbo].CabinCategory cc
WHERE ShipId = @ShipId;


INSERT INTO CabinToBenefits (
  [BenefitsId]
 ,[CabinCategoryId]
 ,[ShipId]
)
SELECT B.[BenefitsId]
      ,bse2.CabinCategoryId
      ,@NewShipId
FROM [dbo].[CabinToBenefits] B
JOIN dbo.CabinCategory bse on B.CabinCategoryId = bse.CabinCategoryId
JOIN dbo.CabinCategory bse2 on bse2.[Category] = bse.Category and B.ShipId = bse2.ShipId
WHERE ShipId = @ShipId;

---- CREATING COPY OF RestaurantToSeating
INSERT INTO RestaurantToSeating (
  ShipId,
  RestaurantId,
  SeatingId
)
SELECT @NewShipId,
       bse.RestaurantId,
       rs.SeatingId 
FROM RestaurantToSeating rs
JOIN dbo.Restaurant r on rs.RestaurantId = r.RestaurantId 
JOIN dbo.Restaurant bse on r.Category = bse.Category and rs.ShipId = bse.ShipId
where rs.ShipId = @ShipId;

uj5u.com熱心網友回復：

正如評論中提到的，rscopy和添加一列是不必要的。您的“創建副本”代碼夾可以替換為以下代碼

---- CREATING COPY OF RestaurantToSeating

insert into RestaurantToSeating (ShipId, RestaurantId, SeatingId )
select ShipId       = @NewShipId,
       RestaurantId = bse.RestaurantId,
       SeatingId    = rs.SeatingId 
from   RestaurantToSeating rs
       inner join dbo.Restaurant r   on rs.RestaurantId = r.RestaurantId 
       inner join dbo.Restaurant bse on r.Category      = bse.Category 
                                    and rs.ShipId       = bse.ShipId
where  rs.ShipId = @ShipId

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/474605.html

標籤：sql服务器 tsql asp.net 核心存储过程

上一篇：自定義層次結構

下一篇：計算SQL中字串中特殊單詞的出現次數