Cannot delete rows from a temporal history table

by

If you make the DELETE dynamic, your stored procedure will successfully ALTER the table, DELETE the records in question, and then ALTER it back.

CREATE PROCEDURE [dbo].[OrderHistoryDelete]
     (@Id UNIQUEIDENTIFIER)
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)

    BEGIN TRANSACTION

        ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF )

        SET @sql="DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) 
        WHERE [Id] = "'' + CAST(@Id AS VARCHAR(40)) + ''''
        EXEC (@sql)

        ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[OrderHistory]))

    COMMIT TRANSACTION
END

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. SQL server query to output the data shown below
  3. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  4. How do I generate a random number for each row in a T-SQL select?
  5. Combine multiple results in a subquery into a single comma-separated value
  6. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  7. Floor a date in SQL server
  8. How to drop a table if it exists?
  9. Create a view with ORDER BY clause
  10. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  11. INSERT INTO vs SELECT INTO
  12. Possible to store value of one select column and use it for the next one?
  13. How FOR XML PATH(”) works when concatenating rows
  14. Scheduled run of stored procedure on SQL server
  15. SQL Server ORDER BY date and nulls last
  16. tsql returning a table from a function or store procedure
  17. SQL Server variable columns name?
  18. What is the Oracle equivalent of SQL Server’s IsNull() function?
  19. SQL Recursive CTE: Finding objects linked by property
  20. PRINT statement in T-SQL
  21. How do you determine what SQL Tables have an identity column programmatically
  22. T-sql – determine if value is integer
  23. Convert decimal time to hours and minutes
  24. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  25. FOR JSON PATH results in SSMS truncated to 2033 characters
  26. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  27. How to set value to variable using ‘execute’ in t-sql?
  28. comma delimited list as a single string, T-SQL [duplicate]
  29. SQL Server error “Implicit conversion of because the collation of the value is unresolved due to a collation conflict.”
  30. INSERT INTO @TABLE EXEC @query with SQL Server 2000

Leave a Comment