Truncate Temporal Table Dynamically

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[TruncateTemporalTables]
(@sName VARCHAR(50)
,@tName VARCHAR(50)
)
/*
Purpose: To truncate a temporal table.
This assumes the history table is simply "TableName" + "History"
This enables reseeding the tables, something a simple delete does not do.

EXEC [dbo].[trunc_TemporalTable] 'nbc','Budget'


ALTER TABLE [nbc].[Budget] SET (SYSTEM_VERSIONING = OFF);
TRUNCATE TABLE [nbc].[Budget];
TRUNCATE TABLE [nbc].[BudgetHistory];
ALTER TABLE [nbc].[Budget] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [nbc].[BudgetHistory]) );



*/

AS

BEGIN

SET NOCOUNT ON;

DECLARE @s1 VARCHAR(255)
  , @s2 VARCHAR(255)
  , @s3 VARCHAR(255)
  , @s4 VARCHAR(255);

SET @s1 = 'ALTER TABLE [' + @sName + '].[' + @tName + '] SET (SYSTEM_VERSIONING = OFF);'
SET @s2 = 'TRUNCATE TABLE [' + @sName + '].[' + @tName + '];'
SET @s3 = 'TRUNCATE TABLE [' + @sName + '].[' + @tName + 'History];'
SET @s4 = 'ALTER TABLE [' + @sName + '].[' + @tName + '] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @sName + '].[' + @tName + 'History]) );'

--PRINT @s1
--PRINT @s2
--PRINT @s3
--PRINT @s4

EXECUTE(@s1);
EXECUTE(@s2);
EXECUTE(@s3);
EXECUTE(@s4);

END

GO