0

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')
    
  • What happens if you manually run the INSERT statement in your SP? – NickW Mar 31 '23 at 10:26
  • Works good for me https://dbfiddle.uk/Oob28LIj – Serg Mar 31 '23 at 10:43
  • @NickW if I do the following the result is as expected and required: `code` USE [MY_DATABASE] GO DECLARE @json NVARCHAR(MAX); SET @json = N'{"UserId": "MyUserId"}'; INSERT INTO [dbo].[MyTable] ([UserID]) SELECT * FROM OPENJSON (@jsonVariable) WITH ( UserId char(7) ) – Wilfred Damhuis Mar 31 '23 at 11:34
  • @Serg can you tell what you do here? In my opinion the inclusion of MyDateTime is superfluous: `code` exec [dbo].[AddUserId] '{"UserId" :"uid1", "MyDateTime":2}'; select * from [dbo].[MyTable]; – Wilfred Damhuis Mar 31 '23 at 11:52
  • @WilfredDamhuis, Yes json sample contains more fields than number of columns in WITH. Just to show extra fields are ignored. – Serg Mar 31 '23 at 19:45

0 Answers0