[SOLVED] Get SQL Insert to work when PK is supplied or NOT

Get SQL Insert to work when PK is supplied or NOT

I have the following stored procedure:

ALTER Procedure dbo.APPL_ServerEnvironmentInsert
(
    @ServerEnvironmentName varchar(50),
    @ServerEnvironmentDescription varchar(1000),
    @UserCreatedId uniqueidentifier,
    @ServerEnvironmentId uniqueidentifier OUTPUT
)
WITH RECOMPILE
AS
    -- Stores the ServerEnvironmentId.
    DECLARE @APPL_ServerEnvironment TABLE (ServerEnvironmentId uniqueidentifier)

    -- Insert the data into the table.
    INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX)
    (
        ServerEnvironmentName,
        ServerEnvironmentDescription,
        DateCreated,
        UserCreatedId
    )
    OUTPUT Inserted.ServerEnvironmentId INTO @APPL_ServerEnvironment
    VALUES
    (
        @ServerEnvironmentName,
        @ServerEnvironmentDescription,
        GETDATE(),
        @UserCreatedId
    )

    -- If @ServerEnvironmentId was not supplied.
    IF (@ServerEnvironmentId IS NULL)
    BEGIN
        -- Get the ServerEnvironmentId.
        SELECT @ServerEnvironmentId = ServerEnvironmentId
        FROM @APPL_ServerEnvironment
    END

The ServerEnvironmentId column is a primary key with a default set on it, which is (newsequentialid()).

I need this stored procedure to work for 2 scenarios:

Value supplied for ServerEnvironmentId - WORKS.
Value not supplied for ServerEnvironmentId - DOES NOT WORK - CANNOT INSERT NULL VALUE. I thought by setting a default on this column this would be fine.

Someone please help to ammend this procedure so that it may work for both scenarios. Solution needs to have minimal changes as all sp's currently following this trend.

Solution