Consider the following statement to create a table, note the default value for MyDateTime:
USE [MY_DATABASE]
GO
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_MyDateTime]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MyDateTime] [datetime2](3) NULL,
[UserID] [char](7) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyDateTime] DEFAULT (sysdatetime()) FOR [MyDateTime]
GO
and the following stored procedure with which to insert data:
USE [MY_DATABASE]
GO
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_MyDateTime]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MyDateTime] [datetime2](3) NULL,
[UserID] [char](7) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyDateTime] DEFAULT (sysdatetime()) FOR [MyDateTime]
GO
USE [MY_DATABASE]
GO
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_MyDateTime]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 31-3-2023 11:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MyDateTime] [datetime2](3) NULL,
[UserID] [char](7) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyDateTime] DEFAULT (sysdatetime()) FOR [MyDateTime]
GO
USE [MY_DATABASE]
GO
/****** Object: StoredProcedure [dbo].[AddUserId] Script Date: 30-3-2023 15:02:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [ual].[AddUserId]
@json nvarchar(max)
AS
BEGIN
--SET NOCOUNT ON;
INSERT INTO [dbo].[MyTable]
([UserID])
SELECT * FROM OPENJSON(@json)
WITH ( UserId char(7)
)
END
When executing this stored procedure the default value for MyDateTime is not set. I would appreciate any input as to why, thank you.
A 'manual' insert results in the MyDateTime column being filled with the value for sysdatetime:
USE MY_DATABASE
INSERT INTO [dbo].[MyTable]
([UserID])
VALUES ('MyUserId')