Cannot delete rows from a temporal history table

Question

I've recently discovered temporal tables in SQL Server. I'd like to start using this functionality. However the biggest hurdle is not being able to delete records from it. Due to GDPR compliance this is an absolute must.

Deleting records from a history table obviously leads to the error:

Cannot delete rows from a temporal history table

So to be able to delete records from a history table I have to disable SYSTEM_VERSIONING, then delete, and then re-enable SYSTEM_VERSIONING. Unless there's another way I'm not aware of?

Since it's not possible to use GO's in a stored procedure/SqlCommand, how can I ensure deleting a history record does not mess with other transactions e.g. updates sent to the temporal table during deleting records from the history table will still result in records being added to the history table?

I've tried creating a stored procedure to wrap it in one transaction but this fails because the ALTER TABLE statement disabling the SYSTEM_VERSIONING is not executed yet leading to the same error.

CREATE PROCEDURE [dbo].[OrderHistoryDelete]
     (@Id UNIQUEIDENTIFIER)
AS
BEGIN
    BEGIN TRANSACTION

    ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF )
    -- No GO possible here obviously.

    DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) 
    WHERE [Id] = @Id

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

    COMMIT TRANSACTION
END
GO

Answer 1

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