在提供或不提供 PK 时使 SQL 插入工作

Question

我有以下存储过程：

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

ServerEnvironmentId 列是一个主键，其上设置了默认值，即 (newsequentialid())。

我需要此存储过程适用于 2 个场景：

1. 提供的值ServerEnvironmentId - 有效。
2. 未为 ServerEnvironmentId 提供值 - 不起作用 - 无法插入空值。我认为通过在此列上设置默认值就可以了。

请有人帮助修改此程序，以便它适用于这两种情况。解决方案需要进行最小的更改，因为所有 SP 目前都遵循这一趋势。

Answer 1

如果列未包含在 INSERT 列表中，则默认值仅应用于插入。我建议进行以下不完全微不足道的更改（我已注释掉要删除的行）：

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) 

    IF @ServerEnvironmentName is null
        SET @ServerEnvironmentName = newid()

    -- 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

此过程不会使用默认约束，但如果存在其他可能存在行的位置，则可以将其保留在适当的位置添加到表中。

Answer 2

（我的第一个答案很长，所以这是这个，所以我发布了第二个答案。）

我错过了您正在使用 NewSequentialId。同样，如果在 insert 语句中指定了列，则不会使用分配给该列的任何 DEFAULT 值 [除非您在 INSERT 语句中使用 DEFAULT 关键字，但这仍然是全部或全部 - 您不能说“如果@Var为空则默认”]。我认为您陷入了简单的分支和半冗余代码，大致如下：（

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) 

    IF @ServerEnvironmentId is null
     BEGIN
        --  ServerEnvironmentId not provided by user, generate during the insert
        INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX) 
        ( 
            ServerEnvironmentName, 
            ServerEnvironmentDescription, 
            DateCreated, 
            UserCreatedId 
        ) 
        OUTPUT Inserted.ServerEnvironmentId INTO @APPL_ServerEnvironment 
        VALUES 
        ( 
            @ServerEnvironmentName, 
            @ServerEnvironmentDescription, 
            GETDATE(), 
            @UserCreatedId 
        ) 

        -- Get the new ServerEnvironmentId
        SELECT @ServerEnvironmentId = ServerEnvironmentId 
        FROM @APPL_ServerEnvironment 
     END 

    ELSE
     BEGIN
        --  ServerEnvironmentId is provided by user
        INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX) 
        ( 
            ServerEnvironmentName, 
            ServerEnvironmentDescription, 
            DateCreated, 
            UserCreatedId,
            ServerEnvironmentId
        ) 
        OUTPUT Inserted.ServerEnvironmentId INTO @APPL_ServerEnvironment 
        VALUES 
        ( 
            @ServerEnvironmentName, 
            @ServerEnvironmentDescription, 
            GETDATE(), 
            @UserCreatedId,
            @ServerEnvironmentId
        ) 
     END

为什么在插入期间锁定整个表？）

Answer 3

帮助简化使用 NEWSEQUNETIALID 的 SQL 插入() 列默认