I'm trying to conditionally execute an ALTER TABLE
command (this one, to be precise). However, I can't get the ADD PERIOD
to be conditional and I suspect this might be a bug.
Here's what I've tried to skip execution if the PERIOD
already exists on the table. All of these raise the same error:
Msg 13597, Level 16, State 2, Line xx
Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.
-
Only add the
PERIOD
if it doesn't currently exist:if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory')) ALTER TABLE [dbo].[sysjobhistory] ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
This raised the error.
-
Since the above didn't work, I wanted to make sure my
IF
statement was evaluating correctly so I tried:if 1=2 ALTER TABLE [dbo].[sysjobhistory] ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
But that raised the error as well.
-
Use
SET NOEXEC ON
to stop execution of the code:set noexec ON ALTER TABLE [dbo].[sysjobhistory] ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime) set noexec OFF
Didn't work. The
ALTER TABLE
still raised the error. -
Admit defeat and wrap it in a
TRY/CATCH
and just ignore the error:begin try ALTER TABLE [dbo].[sysjobhistory] ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime) end try begin catch end catch
This raised the error too — which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the
CATCH
block.
Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION
reports:
Microsoft SQL Azure (RTM) – 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation
UPDATE:
I can use a similar technique to skip adding columns that already exist without any issues:
if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
ALTER TABLE [dbo].[sysjobhistory]
ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
GO
The above code only executes if the sysjobhistory
does not contain the column StartTime
. If it already does, the code does nothing and continues executing. The ALTER TABLE
with ADD PERIOD
always causes an execution error even if the code is inside an IF
condition that evaluates to false
.
Best Answer
You need dynamic SQL for DDL like this.