Variable History_Retention_Period for SQL Server System Versioned Tables

Question

I want to be able to setup different retention periods for different environments without having a separate script for each environment. I am currently using the SQL Server Database project in Visual Studio and creating a post deployment script to handle this.

I've setup a Sql cmd variable that gets passed based on the publish profile that I am using that pass in a variable called RetentionPolicyMonths:

  <ItemGroup>
    <SqlCmdVariable Include="RetentionPolicyMonths">
      <Value>36</Value>
    </SqlCmdVariable>
  </ItemGroup>

I would then like to use that in my post deployment script to set the retention policy per table:

DECLARE @RetentionPolicyMonths int = CAST('$(RetentionPolicyMonths)' AS int)

ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = @RetentionPolicyMonths MONTHS));

My issue here is that it will not currently let me set the History_Retentin_Period property using a variable the way I am doing it now. This is the error that I am currently seeing:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@RetentionPolicyMonths'.Expecting -, ID, INT, or NUMERIC

Can I set this using a variable, or can I only supply the number?

Answer 1

The syntax for that ALTER TABLE option is as follows

[, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]

So it looks like you cannot use a variable in that statement.

You can still pass a parameter in, if you use dynamic SQL

DECLARE @sql nvarchar(max) = '
ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = ' + CAST(@RetentionPolicyMonths AS varchar(10)) + ' MONTHS));
';

EXEC (@sql);

But you appear to actually be interpolating the value into the query anyway (this is not the same as passing a parameter), so you may as well just interpolate that directly, so long as you are sure of its veracity.

ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = '$(RetentionPolicyMonths)' MONTHS));