How to resolve a null Scope_Indentity on cascaded insert?

Question

I'm performing a cascading insert in a SQL Server stored procedure. Then I pass the SCOPE_IDENTITY from the ID of the first table insert to the second table.

But during executing the stored procedure I get a NULL value for the SCOPE_IDENTITY:

Msg 515, Level 16, State 2, Procedure InsertDDM_UserProfile, Line 43
Cannot insert the value NULL into column 'Filter', table '.....dbo.DDM_Dashboard'; column does not allow nulls. INSERT fails.

Question: why does the stored procedure return a null ID using SCOPE_IDENTITY?

This is the stored procedure I've drafted. FK constraints have already been set up for the tables:

ALTER PROCEDURE [dbo].[InsertDDM_UserProfile]
   @p_email VARCHAR(100),
   @p_dashboardName VARCHAR(100),
AS 
BEGIN    
    INSERT INTO [dbo].[DDM_User] ([Email])
    VALUES (@p_email)

    INSERT INTO [dbo].[DDM_Dashboard] ([Dashboard_Name], [DDM_USER_ID])
    VALUES (@p_dashboardName, SCOPE_IDENTITY())
END

And below are the two table's structure:

DDM_User-

CREATE TABLE [dbo].[DDM_User]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Email] [varchar](80) NOT NULL,

    CONSTRAINT [PK_DDMUser] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

DDM_Dashboard:

CREATE TABLE [dbo].[DDM_Dashboard]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Dashboard_Name] [varchar](100) NOT NULL,
    [DDM_USER_ID] [int] NOT NULL,

    CONSTRAINT [PK_DDMDashboard] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DDM_Dashboard] WITH NOCHECK 
    ADD CONSTRAINT [FK_DDMDashboard_DDMUser] 
        FOREIGN KEY([DDM_USER_ID]) REFERENCES [dbo].[DDM_User] ([ID])
GO

ALTER TABLE [dbo].[DDM_Dashboard] CHECK CONSTRAINT [FK_DDMDashboard_DDMUser]
GO

Answer 1

This is nothing to do with SCOPE_IDENTITY(), during your second insert:

INSERT INTO [dbo].[DDM_Dashboard] ([Dashboard_Name], [DDM_USER_ID])
VALUES (@p_dashboardName, SCOPE_IDENTITY())

You are only inserting into two columns [Dashboard_Name], and [DDM_USER_ID]. You do not specify a value for the column [Filter], which from your error message does not allow null values, therefore your insert fails.