"Invalid column name x" and "There is already an object named 'x' in the da-CodePudding

I'm using Swagger API v1 OAS3 to execute a stored procedure in SSMS v15.0.18386 using T-SQL. However, the weird part is that even though I get an error message in my server response, executing it directly via my Swagger UI still works and gives me the intended result. I assume it has something to do with the way I create and drop temporary tables in combination with creating a new column for each temporary table before dropping it. I'd like to just ignore the error message, but I think it's causing my frontend to refuse making it work.

The error message I get from my server via Swagger:

Microsoft.Data.SqlClient.SqlException (0x80131904):
Invalid column name 'Category'.
Invalid column name 'Category'.
Invalid column name 'Category'.
Invalid column name 'Category'.
There is already an object named 'rscopy' in the database.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable1 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)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context) Error Number:207,State:1,Class:16

My T-SQL code:

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

As you can see, I'm using the same code for creating other temporary tables besides the one mentioned in the error message ('rscopy') and yet it's the only one that's underlined as an error. Perhaps because it is the last temp table (with many-to-many variables) I create in my stored procedure?

Either way, given what I have, I can't quite see why my code is causing me this error - especially not when it actually works when I execute it via my API UI.

Any ideas?

CodePudding user response：

As mention in comment, the rscopy and adding a column are not necessary. Your "creating copy" chuck of codes, may be replaced with following

---- 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

CodePudding user response：

Don't copy into a temporary table only to delete it afterwards. Just insert directly from the original.

Also:

Don't roll your own IDENTITY column with SELECT MAX. Use a proper IDENTITY column and get the previous value using OUTPUT or SCOPE_IDENTITY().
Don't quote every column name with [] when it doesn't need it. I haven't bothered removing them because I haven't got all day.
I must say, I'm unclear why you have those joins, they don't appear necessary, but I've left them in.

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

SET NOCOUNT ON;

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

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;