SQL Server Stored procedure deleting entries and only insert one row

5anewei6  于 2023-06-28  发布在  其他
关注(0)|答案(1)|浏览(127)

I am trying to create a stored procedure to delete all rows which are at least 1 old, basically I need to insert new rows and delete old ones, but my code is not working properly. I added a condition, but it's causing an issue here, not sure what is the best way to do it.

Should I create a separate stored procedure for it, or how should I do it?

Here is my stored procedure:

CREATE PROCEDURE [dbo].[add] 
    (@RecordNumber VARCHAR(100) = NULL,
     @DueDate DATETIME = NULL,
     @Id VARCHAR(50) = NULL,
     @DisplayName VARCHAR(100) = NULL,
     @PersonPrimaryEmailAddress VARCHAR(100) = NULL,
     @LastModifiedDate DATETIME = NULL -- this handles when the sp was run and date inserted, the next run date will be different a difference of a hour or less
    )
AS
BEGIN 
    DELETE FROM dbo.iTalent_365_assignments  
    WHERE lastScheduledRunDateTime > DATEADD(hour, -1, GETDATE()) 
  
    INSERT INTO dbo.Offerings (RecordNumber, DueDate, Id,
              DisplayName, PersonPrimaryEmailAddress, LastModifiedDate)
    VALUES (@RecordNumber, @DueDate, @Id,
            @DisplayName, @PersonPrimaryEmailAddress, GETDATE())
END

I am quite sure the delete is just executing it before it inserts but I am confused how should I delete the old rows without the new ones being affected?

wkyowqbh

wkyowqbh1#

The operator on your delete is the wrong direction.

I have this saying, "The pac man eats the bigger number". Your query is deleting newer records, not older ones.

相关问题